Using SQL result as a filter for another query

Question

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'

Please provide sample data, desired results, and a clear explanation of what you are doing. — Gordon Linoff
– Gordon Linoff, Commented Aug 2, 2021 at 11:22
hi @kyros, welcome to stackoverflow. your post nor the title does not really ask a question in its current shape. and also misses some details it seems like "is this your main query" or "is this your filter query". also make sure you copy-paste your code or make sure it is typo-free. — Yılmaz Durmaz
– Yılmaz Durmaz, Commented Aug 2, 2021 at 16:50

Gordon Linoff · Accepted Answer · 2021-08-02 11:24:59Z

1

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;

answered Aug 2, 2021 at 11:24

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kyros Over a year ago

Thanks you, this helped me. I got this as a result. now I want to show the 'HTHP-P' On the first row as something like this. how can I do that? Item_Code2 Item_Code transaction_no Sales Quantity HTHP-P SCARD-CLC 8748 534 1 HTHP-P WPL 43169 0 1 HTHP-Q TG-R 11135 243.48 1 HTHP-Q L888 147006 792.86 1 HTHP-Q CH-SKL 67684 0 1

Gordon Linoff Over a year ago

@kyros . . . You need an order by. If I understands correctly order by transaction_no, (item_code = 'HTTP-P') desc.

AbhiKB · Accepted Answer · 2021-08-02 11:31:43Z

0

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'

answered Aug 2, 2021 at 11:31

AbhiKB

711 silver badge7 bronze badges

Collectives™ on Stack Overflow

Using SQL result as a filter for another query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related