Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aleangelico
Starting Member
4 Posts |
 Posted - 2008-07-25 : 16:23:19
|
| Hi everybody.I want to be able to filter a group of rows if for one column the value is NOT x for EVERY row.I have a table for invoices where i save the payments details. For the same invoice number i have as many rows as payments the client did. For accounting reasons i need to list all the invoices paid fully in cash. For example, the client bought an item for $ 500 and paid $300 with Amex, $100 with a cheque and $ 100 in cash.Another client bought an item por $ 300 and paid fully cash. So this is the table.|InvoiceID | PaymentType| Amount|| 100 | Amex | 300|| 100 | Cheque | 100|| 100 | Cash | 100|| 101 | Cash | 300|I want to make a select returning only the invoice fully paid in cash. So the result should be:|InvoiceID | PaymentType| Amount|| 101 | Cash | 300|Any ideas? I really don't know how to do this...Many thanks Alexwww.novatechinfo.com |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-07-25 : 16:27:32
|
| Select * From MyTable awhere not exists (Select * from Mytable aa where aa.InvoiceID = a.InvoiceID and not aa.PaymentType in ('Cash'))and a.PaymentType = 'Cash' |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-26 : 11:32:48
|
| select * from yourtable tINNER JOIN(SELECT InvoiceIDFROM YourTableGROUP BY InvoiceIDHAVING SUM(CASE WHEN PaymentType='Cash' THEN 1 ELSE 0 END)=1AND COUNT(DISTINCT PaymentType)=1) tmpON tmp.InvoiceID=t.InvoiceID |
|
|
|
aleangelico
Starting Member
4 Posts |
Posted - 2008-07-28 : 15:21:36
|
| Guys, you rock!Thanks!Alex |
|
|
|
|
|
|
Filter all rows if some val not the same in all