5

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?

4 Answers 4

7

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, 2012 at 16:39
-1

I'm sure your objective is different than the application of the query below, but this answers the title of the question, not the content, and works for me as a UNION between 2 tables and a JOIN on the grouped results, good for when you have a backup of historical data in a side table and want to get all data in a single query, although it might be slow for big tables

SELECT PG.category_name, COUNT(distinct(P.id)) 
FROM 
(SELECT id, category_id FROM product_groups 
 UNION 
 SELECT  id, category_id FROM product_groups_backup) AS P
INNER JOIN product_categories PC ON P.category_id = PC.id
GROUP BY PG.category_name

Selecting recursive data in the same table is as JNK says, better done with a LEFT JOIN instead.

-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. Jan 29, 2016 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.