0

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: enter image description here

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

Jeremy Harris
  • 24,318
  • 13
  • 79
  • 133
Radoslav Trenev
  • 353
  • 1
  • 6
  • 17
  • 1
    Have a go... we aren't your slaves – Farkie Dec 30 '15 at 01:02
  • at the very least, you could make an sql fiddle. if you're not going to make an effort to do it yourself at least make an effort to ask a decent question. – I wrestled a bear once. Dec 30 '15 at 01:06
  • 1
    It sounds like you need a `JOIN` query, specifically a `LEFT JOIN`. see http://dev.mysql.com/doc/refman/5.7/en/join.html and http://stackoverflow.com/a/6294854/689579. It will look something like `SELECT table1.*, SUM(table2.field1), ..... FROM table1 LEFT JOIN table2 ON table1.id = table2.id ....`. – Sean Dec 30 '15 at 01:06
  • I'm sorry. I'll try to explain myself a little better. It's just that always when i try to explain what i did i sound stupid .. Posting a pic right now – Radoslav Trenev Dec 30 '15 at 01:07
  • if you're worried about sounding stupid then just quit right now. – I wrestled a bear once. Dec 30 '15 at 01:08
  • 2
    Fear is a part of learning. Courage is not an absense of fear, rather the capability to move forward *in spite of* fear. Don't be afraid to ask a proper question -- you will be judged much more for *not* asking a good question. – Jeremy Harris Dec 30 '15 at 01:09
  • Can you provide some sample data and your desired outcome? It would be even better if you use http://sqlfiddle.com/ to provide your sample data. – Sean Dec 30 '15 at 01:15
  • @Sean [link](http://sqlfiddle.com/#!9/c9a6e/1) here i think this might be helpful. All i want to do now is add a column for total amount from invoice_items, working the same way as amountpaid ----- EDIT ------ [LINK2](http://sqlfiddle.com/#!9/cc2dc/1) you can see how the amounts double when i add the second join – Radoslav Trenev Dec 30 '15 at 01:35

1 Answers1

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

Guillaume F.
  • 5,905
  • 2
  • 31
  • 59