Retrieve transaction report 2

[soushi01]

Hi

I currently do transaction report by cash , criteria is any "transaction by 'cash' " must display in report.
other transaction like Cheque no need display

table_collection
IC-------- Item------------ Receipt--- Mode_Of_Payment----- Transaction_STATUS ------ COLLECTED_DATE
T123----- Addidas-------- rc01------- Cheque------------------ Cancel----------------- 12-dec-2012
T123----- Addidas-------- rc01------- Cancel Transcation------ Cancel---------------- 12-dec-2012
B567----- Nike------------- rc02------- Cash--------------------- Cancel---------------- 12-dec-2012
B567----- Nike------------- rc02------- Cancel Transcation------ Cancel---------------- 12-dec-2012
C890----- New Balance--- rc03------- Cancel Transcation------ Cancel--------------- 12-dec-2012
C890----- New Balance--- rc03------- Cheque------------------ Cancel--------------- 12-dec-2012
D112----- Reebok---------- rc04------- Cash------------------ Active--------------- 12-dec-2012

Expected output
IC--------- Item----------- Receipt--- Mode_Of_Payment------- Transaction_STATUS------ COLLECTED_DATE
B567------ Nike----------- rc02------- Cash----------------------------- Cancel-------------- 12-dec-2012
B567------ Nike----------- rc02------- Cancel Transcation------------ Cancel------------- 12-dec-2012
D112------ Reebok---------- rc04------- Cash------------------------- Active------------------ 12-dec-2012

thanks

 

[JarlH]

I guess you want something like:

[tt]SELECT *
FROM table_collection
WHERE IC IN (SELECT IC FROM table_collection WHERE Mode_Of_Payment = 'Cash')
[/tt]
i.e. all rows for a transaction where cash has been involved at least once.

 

[PHV]

Another way:

SELECT T.*
FROM table_collection T INNER JOIN (
SELECT DISTINCT IC FROM table_collection WHERE Mode_Of_Payment='Cash'
) C ON T.IC=C.IC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JarlH]

Yeah, thats a part of the beaty of SQL, the same problem can be solved in completely different ways. (Somewhat confusing for beginners?)

Note that my solution is Core SQL-99 compliant!
(

http://developer.mimer.com/validator/parser99/,

a great time saver when writing "portable" SQL.)