Search code examples
sqlsql-serversubqueryleft-joinwindow-functions

Selecting data with where exists

I have three tables which i LEFT JOINed. In this case i needed to use COALESCE(ID, ID1) to replace missing IDs with ID1s.

The goal is to get just the IDs and all of their related data entries, given value 'w' in column OPS is present.

I tried using EXISTS clause but it just returns only rows where the value of column OPS is 'w' and not the other rows related to the same ID:

SELECT t1.id, t1.age, t2.operation, t3.ops
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON COALESCE(t2.id, t2.id1) = t1.id 
LEFT JOIN table3 AS t3 ON COALESCE(t3.id, t3.id1) = t1.id
WHERE EXISTS (SELECT 1 
              FROM table3 
              WHERE id = t1.id
                AND (t3.ops = 'w'))

Now I try to figure out why my EXISTS clause does not work properly.

Data:
db<>fiddle

This is the result i wanted to produce with the code:

id age operation ops
1 23 A q
1 23 A w
1 23 A e
1 23 B q
1 23 B w
1 23 B e
1 23 C q
1 23 C w
1 23 C e
2 25 A q
2 25 A w
2 25 B q
2 25 B w
4 43 A q
4 43 A w
4 43 B q
4 43 B w
Solution

  • The window function as specified by GMB will work, but also, I think there is a bit of confusion around using the EXISTS clause.

    SELECT t1.id, t1.age, t2.operation, t3.ops
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON COALESCE(t2.id, t2.id1) = t1.id 
LEFT JOIN table3 AS t3 ON COALESCE(t3.id, t3.id1) = t1.id
WHERE EXISTS (
  SELECT 1 
  FROM table3 inner_t3
  WHERE COALESCE(inner_t3.id, inner_t3.id1) = t1.id -- your join above wasn't 
                                                    -- on ID alone. This one 
                                                    -- shouldn't be either
  AND inner_t3.ops = 'w'  -- Make sure you have proper reference to inner 
                          -- table using alias.
)