CODEWITHSUNDEEP
sqlsql-server
AliK
AliK

Reputation: 1067

How can I include a subquery Result in output when using IN clause in SQL Server?

I have the following query:

select x.Currency,x.ProductID,x.Quantity,x.Channel
from
(
select distinct l.Channel, l.Quantity,l.Currency
from Products l
and l.ProductID in (select distinct pc.ProductID
                    FROM Tags pc
                    where pc.Tags in
                        (
                            'T1',
                            'T2',
                            'T3'
                        )
                    )
) x
GROUP BY x.Currency,x.ProductID,x.Quantity,x.Channel

Is there a way to include the the Tag that matched in the IN clause along with the output?

So I can have Tag as a column in my output table.

Tried to change it to:

select(distinct pc.ProductID,pc.Tag) but does not work.

Upvotes: 0

Views: 49

Answers (1)

learning
learning

Reputation: 610

If you want to select Tag, you can change subquery to use JOIN. However, the column in the select statement must be the same as in group by. Thus, you have to group the tag as well

SELECT x.Currency, x.ProductId, x.Quantity, x.Channel, x.Tags
FROM
(
    SELECT DISTINCT a.Channel, a.Quantity, a.Currency, pc.Tags
    FROM Products a
    JOIN Tags pc ON pc.ProductId = a.ProductID
    WHERE pc.Tags IN('T1','T2','T3')
) x
GROUP BY x.Currency, x.ProductID, x.Quantity, x.Channel, x.Tags

Upvotes: 1

Related Questions