CODEWITHSUNDEEP
mysqlsqljoinleft-join
Hein
Hein

Reputation: 921

SQL JOIN - don't get it

I can't figure out how to get the results I need. Can someone help?

These are the mySql tables I have:

restaurant           menu                  menu_has_dishes             dishes
----------           ----                  ---------------             ------
id (k)        <->    restaurantid          id (k)                      title
name                 id (k)       <->      menuid                      price
                     date                  dishid             <->      id (k)

I need to get all menus with their respective list of dishes for a certain day, belonging to a restaurant. Can anyone show me the right SQL query for that and maybe explain it...?

Thanks... :)

Upvotes: 1

Views: 129

Answers (4)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112279

SELECT r.name, m.date, d.title, d.price
FROM
    restaurant r
    INNER JOIN menu m
        ON r.id=m.restaurantid
    INNER JOIN menu_has_dishes md
        ON m.id=md.menuid
    INNER JOIN dishes d
        ON md.dishid=d.id
WHERE
    r.id = 123        -- OR r.name = 'abc'
    AND
    m.date = CONVERT(date,'2011/12/06');

Upvotes: 0

hungneox
hungneox

Reputation: 9829

SELECT res.id, res.name,m.id,m.date FROM restaurant res
JOIN menu m ON m.restaurantid = res.id
JOIN menu_has_dishes mhd ON mhd.menuid = m.id
JOIN dishes d ON d.id = mhd.dishid
WHERE res.id = ?

Upvotes: 0

Cylindric
Cylindric

Reputation: 5894

This will returns all the fields in the tables, for restaurant '5' on a date:

SELECT *
FROM restaurant r
INNER JOIN menu m ON (r.id=m.restaurantid)
INNER JOIN menu_has_dishes md ON (m.menuid=d.menuid)
INNER JOIN dishes d ON (md.dishid=d.id)
WHERE r.id = 5
AND m.date = '2011-12-07'

You might just want this to get the pertinent information:

SELECT r.id, r.name, m.date, d.title, d.price

Upvotes: 1

ean5533
ean5533

Reputation: 8994

SELECT d.title, d.price
FROM dishes d
JOIN menu_has_dishes md ON d.id = md.dishid
JOIN menu m ON md.menu_id = m.id
WHERE m.restaurantid = [[your restaurant id goes here]]

This will give you output something like this:

-----------
|Fish|1.00|
|Ham |2.00|
|... |... |
-----------

So let's break down the pieces of this query:

--Tell the database what fields you care about
SELECT d.title, d.price

--Tell it what table to start with, and give it a nickname "d"
FROM dishes d

--join to menu_has_dishes so we know what menus contain each dish
JOIN menu_has_dishes md ON d.id = md.dishid

--join to menu so we know what restaurant has each menu
JOIN menu m ON md.menu_id = m.id

--finally, tell it what restaurant to look at
WHERE m.restaurantid = [[your restaurant id goes here]]

Upvotes: 2

Related Questions