0

I have a table that contains ids with different deltas as separate rows. So an id can have a 0 delta or delta 1 or both deltas 0 and 1. I am trying to find those ids that have delta 0 but not delta 1.

Those ids must be inside a list of ids that are returned from another query. I can separately retrieve those two sets, but how do I make the cross-reference within the same query?

example:

dataset

id|delta
1 | 0
1 | 1
2 | 0
3 | 0
4 | 1
5 | 1

expected result:

ids 4,5

query

select m.id from table m where m.delta=0 and m.id in (LIST OF IDS FROM ANOTHER QUERY)

and

select n.id from table n where n.delta=1 and n.id in (LIST OF IDS FROM ANOTHER QUERY)
NikOs
  • 52
  • 3
  • 10

1 Answers1

0

Use the LEFT JOIN / WHERE NULL pattern in Return row only if value doesn't exist when joining the table with itself.

SELECT m.id
FROM yourTable AS m
LEFT JOIN yourTable AS n ON m.id = n.id AND n.delta = 1
JOIN (another query) AS o ON m.id = o.id
WHERE m.delta = 0 AND n.id IS NULL
Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • To my understanding though, this would retrieve id's that have both deltas. I only want the id's that have delta 1 and not delta 0. I guess changing the WHERE clause to m.delta <> 0 and n.delta = 1 should do it ? I ll give it a shot, it looks like its gonna do the trick – NikOs Nov 02 '16 at 07:21
  • Not sure if it works on the actual data, on any iteration (d=0, d<>0). My very first result is an id that has 0 and 1 delta. – NikOs Nov 02 '16 at 07:32
  • The question says "have delta 0 but not delta 1". I've updated the answer to do that. It uses the `LEFT JOIN` pattern from http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 – Barmar Nov 02 '16 at 19:35