21

I would like to combine two select queries with UNION.
How can I use the result from the first SELECT in the second SELECT?

(SELECT carto_id_key FROM table1
    WHERE tag_id = 16)
UNION 
(SELECT * FROM table2
    WHERE carto_id_key = <the carto_id result from above> )
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Bwyss
  • 1,736
  • 3
  • 25
  • 48
  • what do you actually want to achieve, this can be with a simple JOIN or a Subquery (if you just wish to get the required details), also the columns both of your queries dont match !! – Akash Apr 06 '13 at 10:14
  • I want to get all the records in table2 that meet the condition of the first SELECT – Bwyss Apr 06 '13 at 10:26
  • if that's the only requirement, read http://www.postgresql.org/docs/8.3/static/tutorial-join.html – Akash Apr 06 '13 at 10:31
  • After looking at that documentation I found the best solution to be: SELECT * FROM tabl1 INNER JOIN table2 ON (table1. carto_id_key = table2. carto_id_key) WHERE table2.tag_id = 16 thanks for the help – Bwyss Apr 06 '13 at 14:20

1 Answers1

30

Use a CTE to reuse the result from a subquery in more than one SELECT.

WITH cte AS (SELECT carto_id_key FROM table1 WHERE tag_id = 16)

SELECT carto_id_key
FROM   cte

UNION ALL
SELECT t2.some_other_id_key
FROM   cte
JOIN   table2 t2 ON t2.carto_id_key = ctex.carto_id_key

You most probably want UNION ALL instead of UNION. Doesn't exclude duplicates and is faster this way.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228