PHP MYSQL - Returning values that are not the same

Go To StackoverFlow.com

0

I want to compare fields in 2 tables to see if the fields have the same values.

For e.g.
- 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?
Thanks.

2012-04-04 02:39
by qwerty
what exactly do you mean by compare? If you mean join then your answer is below, otherwise elaborate what you mean by compare - Robert 2012-04-04 03:12


1

A 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

JOIN


EDIT:

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

FULL OUTER JOIN


EDIT:

To show records in products that don't have a match in wishlist, use a LEFT JOIN:

SELECT *
FROM
    products p
    LEFT JOIN wishlist w ON w.userId = p.userId AND w.prodId = p.prodId
WHERE
    w.Id IS NULL

LEFT JOIN

2012-04-04 02:43
by Michael Fredrickson
How would you do this, if you want to find non-matching values (show everything that is not a match) - qwerty 2012-04-04 03:15
Hi thanks for your answers; sorry i've updated my question, would you be able to help me on that scneario please - qwerty 2012-04-04 06:14
Could, you explain to me the last edit that you did - qwerty 2012-04-05 00:02
@qwerty It attempts to join products to matching wishlist records, but only returns records where there is not match found in wishlist (w.Id IS NULL - Michael Fredrickson 2012-04-05 00:42