By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,510 Members | 1,141 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,510 IT Pros & Developers. It's quick & easy.

Limiting results based on comparing two SELECT values

hodgeman
P: 7
My second thread on thescripts, so hoping to get the same feedback and help as last time...

I've developed an online invoicing and payment system for all my accounts for my web design company. I've been extending the functionality tonight and have come across a problem...

I'd like to limit the results based on comparing the total and paid results as below.

QUERY
Expand|Select|Wrap|Line Numbers
  1. SELECT A.id,A.date,A.job,(A.price1*A.qty1+A.price2*A.qty2+A.price3*A.qty3) as total,(SELECT sum(P.amount) FROM accPayments P WHERE P.accountID=A.id) as paid FROM accounts A, accDebtors D WHERE D.id=A.debtorID AND A.debtorID = '2' ORDER BY date
RESULTS
[HTML]
id date job total paid
5 2006-08-08 Setup and Establishment of Sportsbike.co.nz 436 NULL
29 2006-08-12 August Website Hosting and Changes 250 NULL
26 2006-08-28 Flash One Piece Suit Application 550 NULL
27 2006-11-22 Updates to Flash Suit Selector 150 150.00
28 2006-11-28 Modifications to Quasimoto Sizes 87.5 NULL
33 2007-02-06 4 New Suits Added to Flash App 700 NULL
39 2007-03-19 Domain Name Renewal 50 NULL
51 2007-05-23 New Quasimoto Revamp CMS 2220 2200.00
64 2007-07-26 Sportsbike.co.nz Annual Renewal 160 NULL
72 2007-09-05 Aggressor, Predator and Stealth Suits 900 900.00
89 2007-11-19 Alterations to MM (Forarm) 150 NULL
99 2008-01-06 Sportsbike Forum Upgrade + Size Charts 150 150.00
110 2008-02-26 Changes to Photo Gallery 75 75.00
113 2008-03-12 Domain Name Renewal 50 NULL[/HTML]

So if an account has been paid or if Paid>=Total, then omit from results, make sense? So that you only see accounts that have outstanding money owing in the results.

I've tried QUERY
Expand|Select|Wrap|Line Numbers
  1. SELECT IF((SELECT sum(P.amount) FROM accPayments P,accounts A, accDebtors D WHERE D.id=A.debtorID AND A.debtorID = '2' AND P.accountID=A.id) > (SELECT (A.price1*A.qty1+A.price2*A.qty2+A.price3*A.qty3) FROM accounts A, accDebtors D WHERE D.id=A.debtorID AND A.debtorID = '2'), (SELECT A.id,A.date,A.job,(A.price1*A.qty1+A.price2*A.qty2+A.price3*A.qty3) as total,(SELECT sum(P.amount) FROM accPayments P WHERE P.accountID=A.id) as paid FROM accounts A, accDebtors D WHERE D.id=A.debtorID AND A.debtorID = '2' ORDER BY date),0)
But I get the error #1241 - Operand should contain 1 column(s)

Any ideas?

Ultimately, what I'd want returned based on the results above would be:
[HTML]
id date job total paid
5 2006-08-08 Setup and Establishment of Sportsbike.co.nz 436 NULL
29 2006-08-12 August Website Hosting and Changes 250 NULL
26 2006-08-28 Flash One Piece Suit Application 550 NULL
28 2006-11-28 Modifications to Quasimoto Sizes 87.5 NULL
33 2007-02-06 4 New Suits Added to Flash App 700 NULL
39 2007-03-19 Domain Name Renewal 50 NULL
64 2007-07-26 Sportsbike.co.nz Annual Renewal 160 NULL
89 2007-11-19 Alterations to MM (Forarm) 150 NULL
113 2008-03-12 Domain Name Renewal 50 NULL[/HTML]


Any help or guidance appreciated.
Mar 12 '08 #1
Share this Question
Share on Google+
2 Replies


hodgeman
P: 7
Does anyone have ideas on how this could be achieved?
Mar 14 '08 #2

hodgeman
P: 7
Anyone? - is it that simple that answering it would seam unnecessary?
Mar 25 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.