-1

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
Popppin.piet
  • 95
  • 7
  • 2
    You are `LEFT JOIN`ing to `table3` (aliased as `t3`) but due to your `EXISTS` **require** that `t3.ops = 'w'`; that is *impossible* if there was no row in `table3` found. Is that intentional? Is so, why not use an `INNER JOIN` to `table3`? – Thom A Apr 05 '23 at 11:15
  • On a different note, a clause like `COALESCE(t2.id, t2.id1) = t1.id` will be to detrimental to performance as the clause it not SARGable. If you need to do logic like this then use an `OR` as at least then the data engine has the opportunity to use an index: `t2.id = t1.id OR (t2.id IS NULL AND t2.id1 = t1.id)` – Thom A Apr 05 '23 at 11:18
  • i used left join on t3 because i can see directly which OPS are related to OPERATION. – Popppin.piet Apr 05 '23 at 11:26
  • 2
    That doesn't address the point. If it's a `LEFT JOIN` *why* is it **required** that `ops` has a non-`NULL` value? By definition it is no longer a `LEFT JOIN` as (to repeat myself) it is **impossible** for `ops` to have a non-`NULL` value if no row in the table was found. Either the join to `table3` should be an `INNER JOIN` or the clause in the `EXISTS` is wrong. – Thom A Apr 05 '23 at 11:29
  • Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) – philipxy Apr 05 '23 at 15:55
  • @Thom A Now i understand what you mean about the non-`NULL` values. The `EXISTS` clause was wrong, of course, because i didn't specify the connection at all. – Popppin.piet Apr 12 '23 at 08:16
  • @philipxy Thank you for sharing the link. The question has already been answered. – Popppin.piet Apr 12 '23 at 08:18
  • I don't know why you think it matters that this has been answered. My comment was left by the system when I voted to suggest this as a duplicate. This post is here forever & the site goal is to be a collection of good Q&A & a zillionth unclear undebugged unresearched duplicate of a question is not helpful. [tour] [ask] [help] – philipxy Apr 12 '23 at 08:41
  • @philipxy I just wanted to say thank you and let you know that the question has been answered FOR ME. That was my only concern. – Popppin.piet Apr 12 '23 at 08:57

3 Answers3

2

If I understand this correctly, I would recommend window functions rather than exist to check whether each group has operation w:

SELECT *
FROM (
    SELECT t1.id AS t1id, 
        t1.age AS t1age, 
        t2.id AS t2id, 
        t2.id1 AS t2id1, 
        t2.operation AS t2operation, 
        t3.id AS t3id, 
        t3.id1 AS t3id1, 
        t3.ops AS t3ops,
        MAX(CASE WHEN t3.ops = 'w' then 1 else 0 end) OVER(PARTITION BY t1.id) has_ops_w
    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
) t
WHERE has_ops_w = 1

In your DB Fiddle:

t1id t1age t2id t2id1 t2operation t3id t3id1 t3ops has_ops_w
1 23 1 1 A 1 1 q 1
1 23 1 1 A null 1 w 1
1 23 1 1 A null 1 e 1
1 23 null 1 B 1 1 q 1
1 23 null 1 B null 1 w 1
1 23 null 1 B null 1 e 1
1 23 null 1 C 1 1 q 1
1 23 null 1 C null 1 w 1
1 23 null 1 C null 1 e 1
2 25 2 2 A 2 2 q 1
2 25 2 2 A null 2 w 1
2 25 2 2 B 2 2 q 1
2 25 2 2 B null 2 w 1
4 43 4 4 A 4 4 q 1
4 43 4 4 A null 4 w 1
4 43 null 4 B 4 4 q 1
4 43 null 4 B null 4 w 1
GMB
  • 216,147
  • 25
  • 84
  • 135
1

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.
)
oglester
  • 6,605
  • 8
  • 43
  • 63
0

Now i understand.
Here again is the EXISTS clause with the advice for better performance that @Thom A has given.
Further, @oglester gave an advice on the convention of names that helped a lot.

SELECT t1.id, t1.age, t2.operation, t3.ops
FROM table1 AS t1
LEFT JOIN table2 AS t2 
  ON t2.id = t1.id OR (t2.id IS NULL AND t2.id1 = t1.id)
LEFT JOIN table3 AS t3 
  ON t3.id = t1.id OR (t3.id IS NULL AND t3.id1 = t1.id)
WHERE EXISTS (
  SELECT 1 
  FROM table3 AS inner_t3 
  WHERE (inner_t3.id = t1.id OR (inner_t3.id IS NULL AND inner_t3.id1 = t1.id))
  AND inner_t3.ops = 'w'
);
Popppin.piet
  • 95
  • 7