select * from statements where client_id = 74 AND
(select paid from invoices where invoices.client_id = statements.client_id AND paid != 'on');
Error : #1242 - Subquery returns more than 1 row
Fair enough.
A quick run down on the structure.
Multiple invoices per statement. Paid notation in invoice table.
Total oweing total in statements table.
How do I merge the results with out duplicates?
(ie, I can extract the data, but it display multiple statement id's, ie one for each invoice)
thats close. It's still showing muitiples. becuase there are multiple invoice amounts per one statement.
could I use a distinct there to edit the limit the output?
SELECT s.id, s.client_id, s.statement_date, s.due_date, s.overdue, s.amount_owed, s.total, i.paid, i.total FROM statements s , invoices i WHERE i . client_id =s . client_id AND s . client_id ='117' AND i . paid !='on' ORDER BY `id` DESC
id client_id statement_date due_date overdue amount_owed total paid total
1469 117 2006-08-01 2006-08-31 on 195.55 184.86
1469 117 2006-08-01 2006-08-31 on 195.55 101.43
1469 117 2006-08-01 2006-08-31 on 195.55 65.36
1469 117 2006-08-01 2006-08-31 on 195.55 98.03
1469 117 2006-08-01 2006-08-31 on 195.55 132.08
1469 117 2006-08-01 2006-08-31 on 195.55 55.81
1469 117 2006-08-01 2006-08-31 on 195.55 69.58
1469 117 2006-08-01 2006-08-31 on 195.55 95
1469 117 2006-08-01 2006-08-31 on 195.55 36.41
1469 117 2006-08-01 2006-08-31 on 195.55 64.79
1469 117 2006-08-01 2006-08-31 on 195.55 71.3
1469 117 2006-08-01 2006-08-31 on 195.55 80.63
1469 117 2006-08-01 2006-08-31 on 195.55 76.54
1469 117 2006-08-01 2006-08-31 on 195.55 66.68
1469 117 2006-08-01 2006-08-31 on 195.55 55.2
1469 117 2006-08-01 2006-08-31 on 195.55 102.61
1469 117 2006-08-01 2006-08-31 on 195.55 70.84
1469 117 2006-08-01 2006-08-31 on 195.55 65.54
1469 117 2006-08-01 2006-08-31 on 195.55 59.17
You need to show me how you are expecting the data to come out - for me the result set you have shown is correct - each row is different, therefore there are not multiple rows...
OK. Sorry.
I only really want the first total. (from the example above $195.55)
The total from the statements table.
The invoice totals are irelavent to what I'm doing.
My trouble (I believe) is that the 'invoice paid' should really be statement paid but I can't change the DB structure.