CODEWITHSUNDEEP
sql
lapots
lapots

Reputation: 13415

get all records from TABLE_A that are not in TABLE_B and with a condition in TABLE_C

Basically the question is to

Get all records from TABLE_A that are not in TABLE_B and has [id] corresponding to field [type] in TABLE_C

The table layout looks like this

TABLE_A
    id
    ref_id

TABLE_B
    id

TABLE_C
    id
    type

Here is my query

SELECT * FROM TABLE_A t1
LEFT JOIN TABLE_B t2 ON t1.id = t2.id
WHERE t1.ref_id IN ['id1', #paramId]
AND type IN (SELECT id FROM TABLE_C WHERE type = #paramType)

It might work (though I did not test) but I do not like that inner select. Is there a way to avoid it using joins?

Upvotes: 1

Views: 57

Answers (1)

sagi
sagi

Reputation: 40491

You can use an INNER JOIN instead :

SELECT t1.* FROM TABLE_A t1
INNER JOIN TABLE_C t3
 ON(t1.type = t3.id and t3.type = #paramType)
LEFT JOIN TABLE_B t2
 ON (t1.id = t2.id)
WHERE  t2.id is null
   AND t1.ref_id IN ('id1', #paramId)

I added this condition:

    WHERE  t2.id is null

To filter out those who exists in table 3.

Upvotes: 1

Related Questions