Mysql – How to combine UNION and INNER JOIN

join;MySQLunion

I'm fetching the grandparent of all nodes in a tree as follows:

SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P             
INNER JOIN product_groups GP ON P.parent = GP.id

There is no entry with id=0 in the table, but some where parent=0, indicating roots of the trees. Those rows are not fetched by this query, as there's no grandparent to join them with. I'd like those rows returned, with 0 (or NULL) for both parent and gp_id.

I was planning to UNION the second table with (0,0), but can't figure out the syntax. How to do this?

Best Answer

Just do a LEFT JOIN instead:

SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P             
LEFT JOIN product_groups GP ON P.parent = GP.id

That will return all rows from P and any non-matching rows in GP will have NULL values.

Related Solutions

Sql-server – Forcing Join Order

It seems that I can specify OPTION (FORCE ORDER) at the end of the query and that will make the joins happen in the right order. There are lots of people warning not to do this because it stops SQL from optimising my query so I deffinately will be using it sparingly (and watching those queries closely).

Mysql – Ask about Inner Join & Union

Try This

SELECT A.*,B.mat_nama,C.mats_nama 
FROM pengeluaran AS A 
INNER JOIN material AS B ON A.mat_id = B.mat_id 
INNER JOIN mat_satuan AS C ON B.mat_satuan_id = C.mats_id
WHERE A.mat_id = '7'
   AND A.pengeluaran_tgl BETWEEN '2014-01-01'  AND '2014-02-28'

UNION

SELECT A.*,B.mat_nama,C.mats_nama
FROM penerimaan_sub AS A 
INNER JOIN material AS B ON A.mat_id = B.mat_id
INNER JOIN mat_satuan AS C ON B.mat_satuan_id = C.mats_id
WHERE A.mat_id = '7'
  AND A.penerimaan_tgl BETWEEN '2014-01-01' AND '2014-01-28'

Best Answer

Related Solutions

Sql-server – Forcing Join Order

Mysql – Ask about Inner Join & Union

Related Question