SQL query to join one to many relation with many to many relation-CodePudding

I have the following structure

COFFEE         ORDER_ITEMS        ORDERS           USERS  
------         -----------        ------           -----
id             order_id           id               id
name           coffee_id          user_id          email

There is a many to many relation between orders and coffees. And a one to many relation between users and orders. I would like to know what coffees a user has ordered.

I have a query to retrieve the orders per user:

SELECT users.id as USERID, orders.id as ORDERID
FROM users
INNER JOIN orders on users.id = orders.user_id

As an example this gives me:

userid  orderid
1       1
1       2
2       3   
4       4

I have a query to retrieve the coffees that belong to an order:

SELECT orders.id, coffees.name
FROM orders, coffees, order_coffees
WHERE coffees.id = order_coffees.coffee_id
AND orders.id = order_coffees.order_id

As an example, this gives me:

orderid coffeename
1       Coffee A
1       Coffee B
2       Coffee A
2       Coffee C

I would like to get the following:

userid  orderid coffeename
1       1       Coffee A
1       1       Coffee B
1       2       Coffee A
2       3       Coffee C    
4       4       NULL

CodePudding user response：

data sheet
1.main order(master_list)
3.Order Details(booking)
3.coffee Meals(shop)
4.user information(user)

SELECT * FROM `booking` as a 
inner join `master_list` as b on a.`mas_id`=b.`mas_id` 
inner join `shop` as c on a.`shop_id`=c.`shop_id`
inner join `user` as d on b.`user_id`=d.`user_id`

I don't know if it helps you try it

CodePudding user response：

You should join all 4 tables starting from the table users and use INNER joins if you want results only for the users who have ordered a coffee at least once, or LEFT joins if you want results for all users, even the ones who have never placed and order.

Use proper joins with ON clauses and aliases for the tables to make the code more readable:

SELECT u.id userid, 
       o.id orderid,
       c.name coffeename
FROM users u
INNER JOIN orders o ON o.user_id = u.id
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN coffees c ON c.id = oi.coffee_id;