Page 1 of 1
Subquery Assistance
Posted: Tue Aug 29, 2006 7:39 am
by facets
Hi All,
I'm having some trouble with a sub query.
Code: Select all
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)
ta, Will
Posted: Tue Aug 29, 2006 7:47 am
by GM
try:
Code: Select all
SELECT s.*, i.paid
FROM statements s, invoices i
WHERE i.client_id = s.client_id
AND s.client_id = '74'
AND i.paid <> 'on'
if it doesn't work, can you post the structure of your tables, and an example of the output you want to see?
Posted: Tue Aug 29, 2006 8:32 am
by facets
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?
Code: Select all
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
Posted: Tue Aug 29, 2006 10:05 am
by GM
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...
Posted: Tue Aug 29, 2006 4:55 pm
by facets
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.
tia.
Posted: Tue Aug 29, 2006 5:16 pm
by feyd
Okay, so take i.total out of the column list and add a distinct?
Posted: Wed Aug 30, 2006 2:49 am
by facets
Hi Ya.
Any idea why this is erroring with..
syntax error, unexpected T_CONSTANT_ENCAPSED_STRING
Code: Select all
$query "SELECT DISTINCT s.*, i.paid FROM statements s, invoices i WHERE i.client_id = s.client_id AND s.client_id = '117' AND invoices.paid != 'on'";
Posted: Wed Aug 30, 2006 3:07 am
by volka
I buy a = and want to solve.
Posted: Wed Aug 30, 2006 3:33 am
by facets
now that's embarassing!