How to combine UNION and INNER JOIN?

Question

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?

Answer 1

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.

Answer 2

SELECT P.id, P.parent, 
       GP.parent, GP.gp_id 
  FROM product_groups P             
       INNER JOIN product_groups GP
          ON P.parent = GP.id
UNION
SELECT P.id, P.parent, 
       0 AS GP.parent, 0 AS gp_id 
  FROM product_groups P
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM product_groups GP 
                    WHERE P.parent = GP.id
                  );