Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I return a row if its ID is not found in another table?

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.

like image 466
Landmine Avatar asked Nov 16 '10 01:11

Landmine


People also ask

How can I get data that is not present in another table?

How to Select All Records from One Table That Do Not Exist in Another Table in SQL? We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries.

How can I get row from another table in SQL?

One SQL code can have one or more than one nested query. Syntax: SELECT * FROM table_name WHERE column_name=( SELECT column_name FROM table_name); Query written after the WHERE clause is the subquery in above syntax.

How do I return rows left table not found in right table?

Now if we look at the question: To return records from the left table which are not found in the right table use Left outer join and filter out the rows with NULL values for the attributes from the right side of the join. Save this answer.


2 Answers

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

Quote:

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.

like image 167
OMG Ponies Avatar answered Sep 28 '22 11:09

OMG Ponies


   SELECT *
     FROM Listing l
LEFT JOIN ListingType t ON l.ID = t.ListingID
    WHERE t.ListingID IS NULL
like image 30
zerkms Avatar answered Sep 28 '22 09:09

zerkms