Query Help

[jeancake]

Hello,

I have a sales transaction table that tracks products sold at different channel (in store, web, & telephone). The channel is identified using a channel id(1,2,3). I would like a query that would show me a unique list of products that were sold in all three channels. I've tried:

SELECT ProductID
FROM SalesTransaction
where ChannelID = 1 and ChannelID = 2 and ChannelID = 3
Group by ProductID

but I get no results. I thought this would be straight forward.

 

[Joe4]

You are telling it to return records where the ChannelID equals 1, 2, and 3 all at the same time. Unless, you are using multi-valued fields, that is not possible and would return nothing.

I think you want to return records where ChannelID equals 1, 2, or 3, i.e.:

SELECT ProductID
FROM SalesTransaction
where (ChannelID = 1) or (ChannelID = 2) or (ChannelID = 3)
Group by ProductID

which can also be written like:

SELECT ProductID
FROM SalesTransaction
where ChannelID in  (1,2,3)
Group by ProductID