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 =
WHERE (ListingID = Listing.ID))))
SELECT t.* FROM LISTING t WHERE NOT EXISTS(SELECT NULL FROM LISTINGTYPE lt WHERE lt.listingid = t.listingid)
SELECT t.* FROM LISTING t WHERE t.listingid NOT IN (SELECT lt.listingid FROM LISTINGTYPE lt)
SELECT t.* FROM LISTING t LEFT JOIN LISTINGTYPE lt ON lt.listingid = t.listingid WHERE lt.listingid IS NULL
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.