CODEWITHSUNDEEP
sqlgoogle-bigquery
kyros
kyros

Reputation: 3

Using SQL result as a filter for another query

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: 1896

Answers (2)

AbhiKB
AbhiKB

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

Gordon Linoff
Gordon Linoff

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

Related Questions