Joining a Quotation, Orders and Customer Table?

Question

I have 3 tables I would like to join to output a single row detailing the quotation number, customer name and order totals..

TBL_QUOTATIONS

quotation_id     |   customer_id   |    status     
-----------------+-----------------+---------------
1038                 21                 Open
1039                 22                 Open     

TBL_CUSTOMERS

customer_id      |   name          |    status     
-----------------+-----------------+---------------
21                  David               Active 
22                  Alvin               Active      

TBL_ORDERS

order_id     |   quote_id   |    desc             |    amount   
-------------+--------------+---------------------+-------------
1                 1038          Consultation           1500
2                 1038          Design Fees            1200
3                 1038          Misc Fees               500

Would need help to get the statement to do the above correctly... Currently I am using

SELECT tbl_quotations.quote_id, customers.customer_id, name, sum(amount) FROM tbl_quotations INNER JOIN customers ON tbl_quotations.customer_id = tbl_customers.customer_id Join orders ON tbl_quotations.quote_id = tbl_orders.quote_id

RESULT

quote_id     |   customer_name   |    sum(amount)             
-------------+-------------------+-------------------
1038              David              3200            
1039              Alvin                 0
 .                 .                   .
 .                 .                   .
 .                 .                   .

I am not sure where i went wrong but apparently the statement is only returning one row even if you had more quotes in the database.

Any help or advice on where I went wrong? Is my approach even appropriate? Thank you!

Answer 1

Try

SELECT tbl_quotations.quote_id, customers.customer_id, name, sum(amount) 
FROM tbl_quotations INNER JOIN customers ON tbl_quotations.customer_id = tbl_customers.customer_id Join orders ON tbl_quotations.quote_id = tbl_orders.quote_id
GROUP BY tbl_quotations.quote_id