0

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.

user4212
  • 1
  • 4
  • With LEFT JOIN you need to filter in the ON clause and not in the WHERE clause... if you use the WHERE clause the LEFT JOIN is converted into a INNER JOIN – Raymond Nijland Sep 01 '17 at 13:21
  • 1
    For next time, see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Sep 01 '17 at 13:35
  • Thank you! Re. MCVE, also thank you, and noted. – user4212 Sep 01 '17 at 23:13
  • Possible duplicate of [Finding unmatched records with SQL](https://stackoverflow.com/questions/508509/finding-unmatched-records-with-sql) – philipxy Sep 02 '17 at 23:57
  • Possible duplicate of [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) – philipxy Sep 03 '17 at 00:10
  • Google 'stackoverflow (sql OR database) join how do I get rows that are not paired with'. This is a faq, because people do not google many clear, concise specific ways of stating their problem/solution/question. If you don't find an answer in many hits, use one as your title. Observe that using your 'How do I get exclusively the texts that are not paired with A...' generates spurious locking hits from 'exclusively' while 'texts' & 'A' are too specific; indeed using 'how do i get only the values that are not paired table' gives your answer 1st hit. So investigate how to google. – philipxy Sep 03 '17 at 00:11

1 Answers1

1

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 
                 );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786