Subquery Assistance

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Subquery Assistance

Post 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
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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?
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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...
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Okay, so take i.total out of the column list and add a distinct?
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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'";
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

I buy a = and want to solve.
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

now that's embarassing!
Post Reply