Archived

This topic is now archived and is closed to further replies.

Sander

Simple SQL query?

Recommended Posts

I need a really simple SQL query but I can''t seem to figure it out I have a table (table1) with all my products listed. It has an ID field (let''s say there are 5 products. Product 1, 2, 3, 4 and 5). Another table (table2, also with ID field) contains all products that have changed in the last week (Say it containts products 1, 2 and 3). Now, I need and SQL query that will give me all the products that have NOT been changed. That is: All the ID''s that appear in table1 but NOT in table2. In this case, I should get back ID''s 4 and 5. So, how do I say this in SQL?
Lone Wolves Game Development Sander Maréchal [Lone Wolves][Game Developers Emporium][E-mail] [Hosting $7,95/mo][Forum FAQ][Google]

Share this post


Link to post
Share on other sites
Assuming the tables are called "Products" and "Changes", and that the field is called "ProductID" in both tables:


SELECT ProductID FROM Products
WHERE NOT (ProductID IN (SELECT ProductID FROM Changes));

Share this post


Link to post
Share on other sites
Two other examples are:

#1
SELECT ID
FROM Products
WHERE NOT EXISTS(
SELECT ID
FROM Changes

#2
SELECT ID
FROM Products LEFT OUTER JOIN Changes
ON Products.ID = Changes.ID
WHERE Changes.ID IS NULL

Share this post


Link to post
Share on other sites