decoder decoder - 2 months ago 6
SQL Question

sql join with not in column include in result

I have two table like

id Name allocation
2 Ash 15
3 Alam 18
4 Rifat 20


and

Date Id Present
24 2 10
24 3 15
25 2 10
25 3 12
25 4 12


Now i want to get the following result

Date Id Alloc Present
24 2 15 10
24 3 18 15
24 4 20 NULL
25 2 15 10
25 3 18 12
25 4 20 12


I've tried
JOIN
query but it does not give desired result
How to get the above result?

Answer
SELECT 
    t1.id
  , dd.date
  , t1.allocation
  , t2.present 
FROM 
    table1 AS t1                         --- all items
  CROSS JOIN
    ( SELECT DISTINCT date
      FROM table2
    ) AS dd                              --- all dates
  LEFT JOIN
    table2 AS t2                         --- present allocations
      ON  t2.id = t1.id
      AND t2.date = dd.date ;

Tested at SQL-Fiddle: test (thank you @JW.)