Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
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
                  );
share|improve this answer
    
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
    
My answer satisfies the spec and is semantically the same as the answer you accepted (except mine returns zeros rather than nulls, as the spec allows). –  onedaywhen Mar 23 '12 at 9:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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