CODEWITHSUNDEEP
sqlpostgresql
jonmrich
jonmrich

Reputation: 4323

"Multiple" searches with Postgres

Basically, I'm trying to get a value from one table and then search for that value in 3 other tables. But I want to do this all in one query.

select user_id from users where email = '[email protected]

The value from that search needs to go into a query like:

SELECT *
FROM table1
JOIN table2 ON (table1.user_id = 
table2.user_id)
WHERE table1.user_id  =  <<<THE RESULTS FROM THE FIRST QUERY>>>
OR table2.user_id = <<<THE RESULTS FROM THE FIRST QUERY>>>

If the user_id isn't in the first table will this JOIN still work?

Upvotes: 0

Views: 46

Answers (1)

StuartLC
StuartLC

Reputation: 107237

A CTE - Common Table Expression (which AFAIK is always materialized in Postgres) will serve the purpose as a placeholder for your first query, which you can then use to join into the other tables. Further, a UNION sounds like what you want as the OR style finding matching data in one or more of the tables t1 .. t3, e.g.:

WITH cteUsers AS
(
    select user_id from users where email = '[email protected]'
)
SELECT t1.user_id, t1.othercol, ...
FROM table1 t1 INNER JOIN cteUsers cte on t1.user_id = cte.user_id
UNION
SELECT t2.user_id, t2.othercol, ...
FROM table1 t2 INNER JOIN cteUsers cte on t2.user_id = cte.user_id
UNION
SELECT t3.user_id, t3.othercol, ...
FROM table1 t3 INNER JOIN cteUsers cte on t3.user_id = cte.user_id;

With the notes:

  • The number, and the types returned in the othercol columns in the various tables t1..t3 must match.
  • If the same user is matched in multiple tables with the same othercol values, then UNION will have the effect of removing duplicates (similar to DISTINCT). Replace this with UNION ALL if you want the duplicate rows.
  • If more than one table t1 .. t3 succeeds in matching the join to users, then this will return one row per table matched (unless it is removed by the distinct, as per above)
  • If there are no matches in any of tables 1, 2 or 3, then the query will return nothing at all. If you want a single row returned from users irrespective of a match (with nulls for unmatched columns), then a LEFT JOIN between users and at least one of the tables t1..t3 is needed

Edit - Re: Ensuring that a users row always returns at least one record / indicate which table(s) are matched

As hinted above, you can use a LEFT OUTER JOIN to handle the case where there's no matches in any of the 3 tables at all. Here, I've rolled up the output of the 3 table matches into another CTE, and then do one final LOJ between the CTE's, with a coalesce to highlight where the join fails (you can obviously also leave this null, if required):

WITH cteUsers AS
(
    -- Replace with bob to see a match in 2 tables
    -- Replace with fred to see a match in 1 table.
    select user_id from users where email = '[email protected]' 
),
cteTables AS
(
  SELECT t1.user_id, 'MatchedTable1' as match, t1.othercol
  FROM table1 t1 INNER JOIN cteUsers cte on t1.user_id = cte.user_id
  UNION
  SELECT t2.user_id, 'MatchedTable2' as match, t2.othercol
  FROM table2 t2 INNER JOIN cteUsers cte on t2.user_id = cte.user_id
  UNION
  SELECT t3.user_id, 'MatchedTable3' as match, t3.othercol
  FROM table3 t3 INNER JOIN cteUsers cte on t3.user_id = cte.user_id
)
SELECT u.user_id, coalesce(match, 'Not Matched At All') as matched, t.othercol
FROM cteUsers u LEFT OUTER JOIN cteTables t ON u.user_id = t.user_id;

I've put a SqlFiddle up here which will hopefully clear things up?.

Upvotes: 1

Related Questions