CODEWITHSUNDEEP
mysqlsqldatabase
YuHe Long
YuHe Long

Reputation: 23

How to join two table with non-existing rows

I have two tables

a schema table:

ID | ITEM
-----------
01  | i1 
02  | i2 
03  | i3

And a record table: 

RecordID | ITEM | VALUE
---------------------- 
RE001    | i1   | 100  
RE002    | i2   | 102  
RE003    | i1   | 200  
RE003    | i2   | 220

I want to get a result table that looks like this

RecordID | ITEM | VALUE
 ---------------------- 
RE001    | i1   | 100  
RE001    | i2   | null  
RE001    | i3   | null  
RE002    | i1   | null  
RE002    | i2   | 102 
RE002    | i3   | null  
RE003    | i1   | 200 
RE003    | i2   | 220  
RE003    | i3   | null

I tried use LEFT JOIN and CROSS JOIN, those return me a merged row with same item that is a null

Is there any methods allow doing such thing?

Upvotes: 2

Views: 98

Answers (1)

pala_
pala_

Reputation: 9010

The answer you want is this:

 select q1.recordid, q2.item, `value`
  from (
    (select distinct recordid 
      from `record`) q1
    cross join
    (select distinct item
      from `schema`) q2
  ) left join `record` r
    on r.recordid = q1.recordid and r.item = q2.item
  order by q1.recordid, q2.item

But due to the cross join in the inner queries it could very quickly become a very huge result set.

Heres the demo

Upvotes: 2

Related Questions