CODEWITHSUNDEEP
sqlsql-server-2008join
decoder
decoder

Reputation: 926

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?

Upvotes: 2

Views: 264

Answers (2)

Bulat
Bulat

Reputation: 6969

There is also an interesting other way:

SELECT 
    t2.date,
    t1.id,
    t1.allocation,
    MAX(CASE WHEN t1.id = t2.id THEN t2.Present ELSE NULL END)
FROM 
    table1 t1, table2 t2
GROUP BY 
    t1.id, t2.date, t1.allocation
ORDER BY 
    t2.date, t1.id

Stolen from: SQL query inner join with 0 values

http://www.sqlfiddle.com/#!3/d2ded/44

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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.)

Upvotes: 4

Related Questions