user1432980 user1432980 - 6 months ago 7
SQL Question

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
join
s?

Answer

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.

Comments