CODEWITHSUNDEEP
sqlsqlite
Alan2
Alan2

Reputation: 24572

How can I find a record which has no matching value in a joined table

I have these SQL Statements:

SELECT COUNT(*) FROM Phrase   -- Returns 660 rows

SELECT COUNT(*) FROM Score    -- Returns 660 rows

SELECT count(*)               -- Returns 660 rows
FROM Score AS S
JOIN Phrase AS P ON S.PhraseId = P.PhraseId

SELECT count(*)               -- Returns 658 rows         
FROM Score AS S
JOIN Phrase AS P ON S.PhraseId = P.PhraseId
JOIN Category AS C ON P.CategoryId = C.Id

I am trying to work out what went wrong in the database and would appreciate some advice on how I could find the phraseId of the records where a phrase has a categoryId that's not in the table Category

Upvotes: 0

Views: 48

Answers (4)

xQbert
xQbert

Reputation: 35333

Use an outer join:

Return all records from score and phrase that match. Include rows from Category that match. Then exclude all of those that had a match on category, leaving you the 2 records from score/phrase that didn't match a category.

SELECT *
FROM Score AS S
JOIN Phrase AS P ON S.PhraseId = P.PhraseId
LEFT JOIN Category AS C ON P.CategoryId = C.Id
WHERE C.ID is null

Not as efficient as not exists/not in but gives you more flexibility on the columns returned.

Upvotes: 1

Guru0008
Guru0008

Reputation: 54

use can use nested subquery to get this

select count(*)
from score as S
where phraseId in 
(select phraseId
    from Phrase
    where phaseId not in (select phraseId from category);

Upvotes: 1

Alex
Alex

Reputation: 1298

I guess you have 2 Phrase records with CategoryId = NULL, assuming there is a referential integrity constraint between Phrase and Category.

Anyway, this should list these 2 Phrase records:

SELECT * FROM Phrase WHERE CategoryId NOT IN (SELECT Id FROM Category)

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

Use NOT EXISTS to find such ids in the Phrase table.

  SELECT * FROM Phrase AS P 
  WHERE NOT EXISTS (SELECT 1 FROM Category WHERE P.CategoryId = C.Id)

Another solution is to use NOT IN however you have to be more careful about possible NULLs

  SELECT * FROM Phrase AS P 
  WHERE P.CategoryId NOT IN(SELECT C.Id FROM Category WHERE C.Id IS NOT NULL)

Upvotes: 2

Related Questions