mysql join query where multiple rows have same key value

Question

I have a table, in a third party database, that has two tables like these:

HISTORY
========
ID | ORDERED
1    PEAS
1    CARROTS
1    SPINACH
2    CARROTS
3    PEAS
3    CARROTS

PEOPLE
=====
ID | NAME
1    Jamal
2    Sharon
3    Mark

I am trying to create a MYSQL query that will return all the PEOPLE who ORDERED both PEAS and CARROTS. The results would be:

Jamal, Mark

When I try this with the OR operator, I get all three people:

SELECT a.ID from people a 
INNER JOIN history b on a.ID=b.ID 
WHERE b.ordered='PEAS' OR b.ordered='CARROTS'

When I try this with the AND operator, I get no people.

SELECT a.ID from people a 
INNER JOIN history b on a.ID=b.ID 
WHERE b.ordered='PEAS' AND b.ordered='CARROTS'

How can I write a query to get the names of the people who ordered peas and carrots given the table structure I have to work with?

Bohemian · Accepted Answer · 2017-05-16 01:02:49Z

2

JOIN twice, once for each condition:

SELECT a.ID
FROM people a 
JOIN history b on a.ID=b.ID AND b.ordered='PEAS'
JOIN history c on a.ID=c.ID AND c.ordered='CARROTS'

If history can contain duplicates, or to be defensive, add DISTINCT:

SELECT DISTINCT a.ID
FROM ...

edited May 16, 2017 at 1:02

answered May 16, 2017 at 0:56

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

2 Comments

Chester Over a year ago

Thank you. This works. There may be other ways to accomplish this, but I am marking this as the correct answer. I also appreciate that, for someone at my level, it made me aware that sequential joins can be used. Thanks again!

Bohemian Over a year ago

@Chester cool. FYI this is also the best performing way of doing it.

score -1 · Accepted Answer · 2017-05-16 02:28:57Z

-1

Select all people from people and for each of them the history details, but only the people who ordered PEAS or CARROTS:

When you say "all" people, then you always begin your sql with the people table. When you say "who have", then you add the LEFT JOIN, e.g to the LEFT table (people) you JOIN the wanted details from the right table (history). And when you say "but just the ones having the following details", then you apply a filter, e.g. you use WHERE clause.

SELECT 
    peo.ID, -- OPTIONAL
    peo.NAME,
    his.ID, -- OPTIONAL
    his.ORDERED
FROM people AS peo
LEFT JOIN history AS his ON his.ID = peo.ID
WHERE 
    his.ORDERED = "PEAS" OR
    his.ORDERED = "CARROTS"
;

Note: "--" means commentar.

EDIT 1:

Important, a principle which you always should apply: Rename the history.ID column to history.PEOPLE_ID and add a new column history.ID as PRIMARY KEY column, in order to uniquely identify each historyrecord.

HISTORY
-------
ID   PEOPLE_ID   ORDERED
1    1           PEAS
2    1           CARROTS
3    1           SPINACH
4    2           CARROTS
5    3           PEAS
6    3           CARROTS

PEOPLE table remains the same.

EDIT 2:

NO no, it should be an AND in the WHERE clause. My fault. I corrected it.

EDIT 3:

NO no, it should be an OR in the WHERE clause. My fault. I corrected it. AGAIN :-)))

edited May 16, 2017 at 2:28

answered May 16, 2017 at 1:23

user7941334

6 Comments

Chester Over a year ago

Thank you. The AND yields zero results. It seems as if this looks for a matching ID in the History table, then looks in that row for peas AND carrots. Thanks for the advice on best practices, there is a unique key in the history table, I just simplified it for the question.

user7941334 Over a year ago

@Chester You're welcome! Now, try without the WHERE clause first and tell me if you're receiving something.

user7941334 Over a year ago

Yes, it should be OR! My mistake. I already corrected from OR to AND :-) Use OR, please.

Chester Over a year ago

Hi, yes I got results without a where clause. Using the OR operator, I get the person who only ordered carrots.

user7941334 Over a year ago

That's strange. Because i run it now with my query from my answer and works perfect: all records except the "spinach" one are displayed.

|

Collectives™ on Stack Overflow

mysql join query where multiple rows have same key value

2 Answers 2

2 Comments

EDIT 1:

EDIT 2:

EDIT 3:

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

EDIT 1:

EDIT 2:

EDIT 3:

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related