Reputation: 3
Here's my code and there's a thousand transaction_no result. Which is I have to use as a filter for another code with the same table.
select Item_Code, Transaction_No, Sales, Quantity
from `transaction_table`
where item_code = 'HTHP-P'
Upvotes: 0
Views: 1895
Reputation: 71
You could use 'in' to filter using the results.
select Item_Code, Transaction_No, Sales, Quantity
from `transaction_table`
where Transaction_No in ( select t.Transaction_No from `transaction_table` t where t.item_code = 'HTHP-P')
or You could store all the results in a temp table and then use the results to filter later in another section of the code.
create table #temp
(
Transaction_No varchar(30)
);
insert into #temp
select Transaction_No from `Tranaction_Table` where item_code = 'HTHP-P'
Upvotes: 0
Reputation: 1271231
You could use in
, if you want to filter on the transactions:
select . . .
from `transaction_table` tt
where tt.transacton_no in (select tt2.Transaction_No
from `transaction_table` tt2
where tt2.item_code = 'HTHP-P'
);
If you want all rows for transactions that have the specified item, you can also use qualify
:
select tt.*
from `transaction_table` tt
where 1=1
qualify countif(tt2.item_code = 'HTHP-P') over (partition by Transaction_No) > 0;
Upvotes: 1