CODEWITHSUNDEEP
sqlsql-server
user6561572
user6561572

Reputation: 339

How to get the unmatching rows in 2 tables in SQL?

Sorry I wrote the question more clearly here: How to get the nonmatching rows in 2 tables in SQL?

Thanks

Upvotes: 0

Views: 77

Answers (3)

Pritam Alamwar
Pritam Alamwar

Reputation: 11

select c.ChanID,C.name 
from Channels c Left join subscribers s on 
c.ChanID=s.ChanID 
where s.ChanID is null

This should work other condition may not required.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

One option uses a LEFT JOIN between the CHANNELS and SUBSCRIBERS tables:

SELECT c.ChanID, c.name
FROM CHANNELS c
LEFT JOIN SUBSCRIBERS s
    ON c.ChanID = s.ChanID
WHERE s.ChanID IS NULL AND
      c.nickname = 'Jonny' AND      -- not sure about this condition
      s.nickname = 'Jonny'          -- or this one

Here is a visual of what the intermediate table looks like during the merge:

c.ChanID | c.name | s.ChanID
1        | first  | 1
2        | second | NULL       <-- the second and third channels don't match
3        | third  | NULL       <-- to anything in the SUBSCRIBERS table (IS NULL)
4        | fourth | 4

Upvotes: 3

jesuisgenial
jesuisgenial

Reputation: 725

   SELECT CHANNELS.ChanID FROM CHANNELS 
    LEFT JOIN SUBSCRIBERS ON CHANNELS.ChanID=SUBSCRIBERS.ChanID
    WHERE
    CHANNELS.type='public' AND
    SUBSCRIBERS.nickname='a'

Try this

Edited: I didn't get the question properly. If you want to select rows that Jonny is not subscribing, use subquery like this.

 SELECT ChanID C FROM CHANNELS WHERE
(SELECT COUNT(*) FROM SUBSCRIBERS S WHERE S.ChanID=C.ChanID and S.nickname='Jonny') = 0

Upvotes: 0

Related Questions