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!