CODEWITHSUNDEEP
phpmysqlsqldatabasejoin
FSmith94
FSmith94

Reputation: 13

Joining 3 Tables on mySQL

Good Day.

I know this question is already asked but I have a hard time implementing my problem.

I want to Join 3 tables. Here is how my tables look like:

Order_Header(Order_Header_ID{PK}, Order_Date, Order_Time, Order_Complete)
Order_Line(Order_Line_ID{PK}, Product_ID{FK}, Order_Header_ID{FK},Quantity)
Products(Product_ID{PK}, Description, Cost)

I Want to JOIN the three tables so that in one table it displays Order_Header_ID, Quantity and Description out of the three tables WHERE Order_Complete = 'YES'. I have the following SQL that displays all the information but do not join them.

SELECT
    Order_Header.Order_Header_ID,
    Products.Description,
    Order_Line.Quantity
FROM Order_Header, Products, Order_Line
WHERE Order_Complete = 'yes'

The idea is that it list a Order_Header_ID once with the corresponding Description and Quantity only once.

Thank you in advance

Upvotes: 0

Views: 44

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

Your current query is missing join conditions. Also, it is preferable to use an explicit join syntax. Correcting for both of these things we can write the following query:

SELECT
    oh.Order_Header_ID,
    p.Description,
    ol.Quantity
FROM Order_Header oh
INNER JOIN Order_Line ol
    ON oh.Order_Header_ID = ol.Order_Header_ID
INNER JOIN Products p
    ON ol.Product_ID = p.Product_ID
WHERE
    oh.Order_Complete = 'yes'

Upvotes: 1

Related Questions