MySQL join statement

Question

Could anyone offer some help please?!...

I have an sql database table called meals and another database table called menu:

meals (id, name, description, cost)
menu (date, option1, option2)

The idea being that there are 2 meal choices in a given day, for example:

09/02/16  meal id 1 OR meal id 8
10/02/16  meal id 4 OR meal id 12

I want to use a join statement similar to this:

select * from meals JOIN menu ON meals.id = menu.option1 AND meals.id=menu.option2

but every time I run this I get an error message, saying table alias not unique.

I want the final output to display:

date: option 1 meal id, name, cost AND option 2 meal id, name, cost

e.g.

09/02/16 - 1(id) Chicken curry £2.50 + 8 (id) Roast Beef £3.00
10/02/19 4 (id) Baked potato £2.50 + 12 (id) Soup/Sandwiches £2.75

Try this select * from meals mea INNER JOIN menu men ON mea.id = men.option1 OR mea.id=men.option2 — Phiter
– Phiter, Commented Feb 9, 2016 at 22:32
@PhiterFernandes - They also probably meant OR, not AND. — Mr. Llama
– Mr. Llama, Commented Feb 9, 2016 at 22:33

Barmar · Accepted Answer · 2016-02-09 22:39:46Z

1

Use OR rather than AND, because you want to match either option, not both in a single meal. This can also be done using IN.

SELECT *
FROM meals
JOIN menu ON meals.id IN (menu.option1, menu.option2)

If you want to get both meals for a date in the same row, use GROUP_CONCAT

SELECT menu.date, GROUP_CONCAT(' + ', CONCAT_WS(' ', meals.id, meals.name, meals.cost)) AS options
FROM meals
JOIN menu ON meals.id IN (menu.option1, menu.option2)
GROUP BY menu.date

answered Feb 9, 2016 at 22:39

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

fredrover · Accepted Answer · 2016-02-10 00:11:39Z

0

Since menu is the entity that you are fetching, and getting the details of each meal based on what's in the menu, it makes sense to start with SELECTing FROM menu.

Option1 and Option2 are two distinct entities, so you'll need to join the meals table two different times instead of using AND or OR:

SELECT * FROM menu 
LEFT JOIN meals AS m1 ON option1 = m1.id 
LEFT JOIN meals AS m2 ON option2 = m2.id

answered Feb 10, 2016 at 0:11

fredrover

3,0352 gold badges20 silver badges25 bronze badges

Collectives™ on Stack Overflow

MySQL join statement

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related