PHP MYSQL - Returning values that are not the same

Question

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.

what exactly do you mean by compare? If you mean join then your answer is below, otherwise elaborate what you mean by compare. — Robert
– Robert, Commented Apr 4, 2012 at 3:12

Michael Fredrickson · Accepted Answer · 2012-04-04 15:56:28Z

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

edited Apr 4, 2012 at 15:56

answered Apr 4, 2012 at 2:43

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

qwerty Over a year ago

How would you do this, if you want to find non-matching values (show everything that is not a match)?

qwerty Over a year ago

Hi thanks for your answers; sorry i've updated my question, would you be able to help me on that scneario please?

Michael Fredrickson Over a year ago

@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)

Collectives™ on Stack Overflow

PHP MYSQL - Returning values that are not the same

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related