3

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?

6

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.

1
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
                  );
1
  • Of course I could union two different selects. I wanted to union the table that I join. The union of multiple complete selects would not work well in my actual case. – Tim Mar 22 '12 at 16:39
-3

This will return all the lines of the two tables. You can change the aliases so that you get the column name you want.

SELECT t1.col1 as name, t1.col2, t1.col3 
FROM t1 

UNION ALL 

SELECT t2.cola as name, t2.colb, null 
FROM t2;
1
  • 1
    This doesn't answer the question. – Colin 't Hart Jan 29 '16 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.