用4个表编写复杂的SQL查询

Question

I have 4 tables from which i want to output information with a single query and i'm not sure how to do that.

• From table1 i want to get all the records

• For each record from table1 i want to take out the SUM from field1 from all records in table2 on a matching id taken from table1

• For each record from table1 i want to take out the SUM from field1 from all records in table3 on a matching id taken from table1

• For each record from table1 i want to take out the value of a single record from table4 on a matching id taken from table1

EDIT:

Here is how i think the graphic for my request should look:

Here's my working code:

SELECT DISTINCT 
   i.id, 
   i.dateCreated,
   i.dateBilled,
   i.dateCompleted,
   i.userId, 
   i.type, 
   i.status, 
   i.truck, 
   i.poNumber, 
   i.total,
   i.billtoId, 
   i.shiptoId, 
   i.invoiceNumber, 
   i.loadNumber, 
   SUM(p.amount) as amountpaid,
   c.name as billtoName
FROM `invoices` as i
LEFT JOIN `invoice_payments` as p ON i.id = p.invoice
RIGHT JOIN `companies` as c ON c.id = i.billtoId
GROUP BY i.id, i.invoiceNumber

You can see how i managed to get the SUM from all payments on my invoices with a left join. I'm trying to do the same for i.total, but as soon as i add another LEFT JOIN my calculations come up wrong and the result in amountpaid doubles

Answer 1

You can write this query with subqueries in the SELECT statement:

SELECT id, 
(SELECT sum(field1) FROM t2 WHERE t2.idfrom1=t1.id) AS firstSum,
(SELECT sum(field1) FROM t3 WHERE t3.idfrom1=t1.id) AS secondSum,
(SELECT min(field1) FROM t4 WHERE t4.id=t1.f2 LIMIT 1) AS singleRecord
FROM t1

This is the idea, you just have to adapt it to your schema.

edit: updated from the drawing