CODEWITHSUNDEEP
sqloracle-database
dropkick
dropkick

Reputation: 23

How can I write this query for an exact value with a multi foreign key table?

I have three tables:

 table_1:

   id                 
   1       
   2

 table_2:

   id   table2_order
   1        1
   2        1
   2        2 

table_3:

 id     table2_order     order     code
 1          1              1        52   
 1          1              2        52
 2          1              1        69
 2          1              2        52
 2          2              1        01

To join these tables this way:

SELECT * FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.id = t2.id
LEFT JOIN table_3 t3 ON t2.id = t3.id AND t2.table2_order = t3.table2_order
WHERE t3.code = '52'

How can I get all values that are 52 and not 69 also? They are searching for the value 52. So they need ids with only code 52. And they can search one to many of the codes. Needs to be exactly codes only. Here I want to get id = 1 but not id = 2 because it has 69.

Upvotes: 0

Views: 44

Answers (1)

Hogan
Hogan

Reputation: 70531

Has 52 only

SELECT * FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.id = t2.id
LEFT JOIN table_3 t3 ON t2.id = t3.id AND t2.table2_order = t3.table2_order and t3.code = '52'
LEFT JOIN table_3 no ON t2.id = no.id AND t2.table2_order = no.table2_order and no.code <> '52'
WHERE no.id is null

This is called an anti-join. It means you only get results for IDs which have code = 52 and don't have code != 52.

Upvotes: 1

Related Questions