user2812678 user2812678 - 23 days ago 5
SQL Question

Search for unique record for columns with multiple identifer

This maybe a little confusing, I'm trying to get a result where it'll only return records without the "F" as tran_type. The problem is some record contains both the "C" and "F" tran_type so if I do a (where tran_type <> "F") it shows all the records (1,2,3,4,5) but I only want record_no 2 and 4 to show. Any assistance is highly appreciated. Thanks

record_no name description tran_type trancode amount
1 user1 apple C 1149 $76.27
1 user1 apple C 1149 $25.00
1 user1 apple F 1164 $(2,500.00)
1 user1 apple C 1161 $(199.76)
2 user2 orange C 1157 $150.00
2 user2 orange C 1158 $(150.00)
3 user3 orange C 1159 $(25.00)
3 user3 orange F 1164 $(1,305.62)
3 user3 orange C 1151 $16.90
3 user3 orange C 1164 $(994.38)
4 user4 orange C 1159 $10.70
4 user4 orange C 1147 $35.00
5 user5 apple C 1149 $5.50
5 user5 apple F 1164 $(50.00)

Answer

Try the following

Select * 
From table1
Where record_no 
Not in (select record_no from table1 where tran_type = 'F')

Assuning that table1 is the table name

Comments