I want to compare fields in 2 tables to see if the fields have the same values.
- I have 2 tables, 'products' and 'wishlist'.
- In both tables, they have the same fields 'prodId' and 'userId', plus other fields like 'title', etc.
- The 'wishlist' table contains some of the same rows that are in table 'products'
(e.g. 10 in total in 'products' but only 6 of the same rows are in table 'wishlist')
- I want to display the fields/rows from table 'products', that are different from table 'wishlist', so from the e.g. it will only show 4 (so it does not show duplicates of the 6),
so to do this I would like to use fields 'prodId' and 'userId', in the table 'products', and compare this to the same fields in the table 'wishlist'.
How would I do this?
JOIN will return you all records that have matching values in both tables:
SELECT * FROM products p JOIN wishlist w ON w.userId = p.userId AND w.prodId = p.prodId
To return all records that are not matching:
SELECT * FROM products p FULL OUTER JOIN wishlist w ON w.userId = p.userId AND w.prodId = p.prodId WHERE p.Id IS NULL OR w.Id IS NULL
To show records in
products that don't have a match in
wishlist, use a
SELECT * FROM products p LEFT JOIN wishlist w ON w.userId = p.userId AND w.prodId = p.prodId WHERE w.Id IS NULL
wishlistrecords, but only returns records where there is not match found in
w.Id IS NULL- Michael Fredrickson 2012-04-05 00:42