MySQL Forums
Forum List  »  Newbie

Query with multiple joins
Posted by: Antoine Dubuc
Date: December 17, 2021 08:10PM

Greetings from Vancouver :)

Say you have three tables:

- purchase orders(po),
- order_items
- users.

A purchase order consist of one or many order_items and both of these tables, purchase orders and order_items, references the id of a user.

In purchase orders its usually an accountant creating the po and in the orders table its usually someone else, a requester, so to speak.

The po table doesn't list anything with regards to order items.
However, the order_items table has, for each item, the id of the po it goes in.
Each of these tables has a userId and I need to retrieve for each the person's name from the user's table.

My goal is to list all po with all order_items along with the name of both the accountant and requester for each po and order_item.

At the core, my question can then be reduced to this: what is the sql code to list:

po.id,
order.id,
po.purchaser(getting the accountant's name from users table from id),
order.requester(getting the requester's name from users table from id).

Please help me avoid thinking about this all weekend :)

Thank you!!!

Antoine

Options: ReplyQuote


Subject
Written By
Posted
Query with multiple joins
December 17, 2021 08:10PM
December 17, 2021 09:29PM
December 19, 2021 04:19PM
December 19, 2021 08:27PM
December 21, 2021 11:56AM
December 21, 2021 02:29PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.