How can I return a row if its ID is not found in another table?
Solution 1
Using NOT EXISTS
SELECT t.*
FROM LISTING t
WHERE NOT EXISTS(SELECT NULL
FROM LISTINGTYPE lt
WHERE lt.listingid = t.listingid)
Using NOT IN
SELECT t.*
FROM LISTING t
WHERE t.listingid NOT IN (SELECT lt.listingid
FROM LISTINGTYPE lt)
Using LEFT JOIN/IS NULL
SELECT t.*
FROM LISTING t
LEFT JOIN LISTINGTYPE lt ON lt.listingid = t.listingid
WHERE lt.listingid IS NULL
Summary
In SQL Server, NOT EXISTS and NOT IN predicates are the best way to search for missing values, as long as both columns in question are NOT NULL. They produce the safe efficient plans with some kind of an Anti Join.
LEFT JOIN / IS NULL is less efficient, since it makes no attempt to skip the already matched values in the right table, returning all results and filtering them out instead.
Solution 2
SELECT *
FROM Listing l
LEFT JOIN ListingType t ON l.ID = t.ListingID
WHERE t.ListingID IS NULL
Solution 3
The following SQL will return all Listing records without a corresponding ListingType record
SELECT *
FROM Listing
LEFT JOIN ListingType ON Listing.ID = ListingType.ListingID
WHERE ListingType.ID IS NULL
Solution 4
Assuming that the rest of your SQL is corret, just add a null check on ListingTypeID:
SELECT Listing.Title, Listing.MLS, COALESCE (Pictures.PictureTH, '../default_th.jpg') AS PictureTH, COALESCE (Pictures.Picture, '../default.jpg') AS Picture, Listing.ID, Listing.Description, Listing.Lot_Size, Listing.Building_Size, Listing.Bathrooms, Listing.Bedrooms, Listing.Address1, Listing.Address2, Listing.City, Locations.Abbrev, Listing.Zip_Code, Listing.Price, Listing.Year_Built, ListingTypeMatrix.ListingTypeID
FROM Listing
INNER JOIN Locations ON Listing.State = Locations.LocationID
LEFT OUTER JOIN ListingTypeMatrix ON Listing.ID = ListingTypeMatrix.ListingID
LEFT OUTER JOIN Pictures ON Listing.ID = Pictures.ListingID
WHERE (ListingTypeMatrix.ListingTypeID = '4' OR ListingTypeMatrix.ListingTypeID IS NULL) AND
((Pictures.ID IS NULL) OR (Pictures.ID =
(SELECT MIN(ID)
FROM Pictures
WHERE (ListingID = Listing.ID))))
Solution 5
I think what you may want to move the ListingTypeMatrix.ListingTypeID = '4'
to the ON clause for ListingTypeMatrix
OUTER JOIN. The clause would look like this
LEFT OUTER JOIN ListingTypeMatrix ON Listing.ID = ListingTypeMatrix.ListingID
AND ListingTypeMatrix.ListingTypeID = '4'
And, you would remove ListingTypeMatrix.ListingTypeID = '4' AND
from the WHERE clause.
This change will only return rows from ListingTypeMatrix that = 4, and join the results to the rest of the query. The LEFT OUTER JOIN indicates that rows from Listing and Locations will not be excluded if rows do not appear in ListingTypeMatrix.
Landmine
Updated on June 08, 2022Comments
-
Landmine about 2 years
I have a 2 tables in a MS SQL 2008 Database, Listings and ListingType, I want to create a select statement that will give me all rows from Listing that do not have their ListingID in the ListingType table.
I'm very confused about how to even start this statement.
Example SQL Statement - Does a lot more than what I explained, but you should be able to get what I'm asking from it.
SELECT Listing.Title, Listing.MLS, COALESCE (Pictures.PictureTH, '../default_th.jpg') AS PictureTH, COALESCE (Pictures.Picture, '../default.jpg') AS Picture, Listing.ID, Listing.Description, Listing.Lot_Size, Listing.Building_Size, Listing.Bathrooms, Listing.Bedrooms, Listing.Address1, Listing.Address2, Listing.City, Locations.Abbrev, Listing.Zip_Code, Listing.Price, Listing.Year_Built, ListingTypeMatrix.ListingTypeID FROM Listing INNER JOIN Locations ON Listing.State = Locations.LocationID LEFT OUTER JOIN ListingTypeMatrix ON Listing.ID = ListingTypeMatrix.ListingID LEFT OUTER JOIN Pictures ON Listing.ID = Pictures.ListingID WHERE (ListingTypeMatrix.ListingTypeID = '4') AND ((Pictures.ID IS NULL) OR (Pictures.ID = (SELECT MIN(ID) FROM Pictures WHERE (ListingID = Listing.ID))))
ListingTypeMatrix.ListingTypeID = '4' is the part I dont know what to change it to, because there will not be a record for it.
-
Wanjia almost 7 years
not excists
ornot in
seems to work better thanleft join
is null