CODEWITHSUNDEEP
mysqlleft-join
user4212
user4212

Reputation: 1

SQL unwanted results in NOT query

This looks like it should be really easy question, but I've been looking for an answer for the past two days and can't find it. Please help!

I have two tables along the lines of 

texts.text_id, texts.other_stuff...
pairs.pair_id, pairs.textA, pairs.textB

The second table defines pairs of entries from the first table.

What I need is the reverse of an ordinary LEFT JOIN query like:

SELECT texts.text_id
FROM texts 
    LEFT JOIN text_pairs
    ON texts.text_id = text_pairs.textA 
WHERE text_pairs.textB = 123 
ORDER BY texts.text_id

How do I get exclusively the texts that are not paired with A given textB? I've tried

WHERE text_pairs.textB != 123 OR WHERE text_pairs.textB IS NULL

However, this returns all the pairs where textB is not 123. So, in a situation like

textA   TextB
1         3
1         4
2         4

if I ask for textB != 3, the query returns 1 and 2. I need something that will just give me 1.

Upvotes: 0

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

The comparison on the second table goes in the ON clause. Then you add a condition to see if there is no match:

SELECT t.text_id
FROM texts t LEFT JOIN
     text_pairs tp
     ON t.text_id = tp.textA AND tp.textB = 123 
WHERE tp.textB IS NULL
ORDER BY t.text_id ;

This logic is often expressed using NOT EXISTS or NOT IN:

select t.*
from texts t
where not exists (select 1
                  from text_pairs tp
                  where t.text_id = tp.textA AND tp.textB = 123 
                 );

Upvotes: 1

Related Questions