Help with SQL query

Hi everyone.

I'm stuck on an SQL query, and hope one of you can help me. Have been trying to solve it all day long without any success. Doesn't even sound that difficult...

I have a table that has four columns. A combination of the first three columns is a foreign key for 'products'. The last column is a foreign key for 'stores'. This table keeps track of which products are assigned to which stores. Example of data -

Id_Prod_Grupo Id_Prod_Tipo Id_Prod_Pres Id_Cliente
---- ---- ---- ----
0 0 1 100
0 0 2 11476
0 0 3 12939
0 0 4 960
0 0 4 12941
0 0 5 1
0 0 5 10
0 0 5 960
0 0 5 15033
0 0 6 1
0 0 6 10
0 0 7 1
0 0 7 15033
0 0 7 92606

In the application, the user selects multiple stores, and the application has to display which all products are common to them. For example, if the user selects store 1 and 10, then application has to pick up products (0,0,5) and (0,0,6). Simple enough right?

Unfortunately, I can't form the query. Can someone please help me with this? If you could just give me a query that works for stores 1 and 10, I'm sure I can modify it myself in the application to make the whole process dynamic.

Thanks in advance folks!Table definitions and some sample data (INSERTs) would be helpful.|||drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)|||Originally posted by sbaru
Table definitions and some sample data (INSERTs) would be helpful.

Actually the table structure etc. are completely irrelevant. Forget the whole part about the foreign keys etc... The only table to be used here is the one I showed above..

Let me see if I can make it any more clear though.. One of the earliest solutions I tried was a query like this -

SELECT DISTINCT Id_Prod_Grupo, Id_Prod_Tipo, Id_Prod_Pres FROM CVR_PRODUCTOS_TIENDA
WHERE Id_Cliente = 1 or Id_Cliente = 10

However, this query simply returns all the products that belong to EITHER one of the stores. I want products which are _common_ to both these stores. Get it?|||Originally posted by snail
drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)

Worked beautifully Snail! Thanks a lot!!|||You didn't like my first solution (http://www.dbforums.com/showthread.php?postid=3656027#post3656027)?|||Originally posted by Pat Phelan
You didn't like my first solution (http://www.dbforums.com/showthread.php?postid=3656027#post3656027)?

Nice try - it works better than mine.|||Originally posted by snail
drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)

Damn.. found a problem. If I use this method to find common products for more than two stores, the queries returns even those products which are present in just two of the stores..|||Originally posted by anujjain
Damn.. found a problem. If I use this method to find common products for more than two stores, the queries returns even those products which are present in just two of the stores..

Use Pat Phelan solution - it works like a charm ...|||Pat Phelan, one small problem in the solution you gave me.. I need to select the fourth column (store) as well, and I can't seem to be able to modify your query to do it right.. I'm afraid I suck at SQL!

Help??

P.S. Thanks a lot for the help guys, you're the best :)|||Originally posted by anujjain
Pat Phelan, one small problem in the solution you gave me.. I need to select the fourth column (store) as well, and I can't seem to be able to modify your query to do it right.. I'm afraid I suck at SQL!

Help??

P.S. Thanks a lot for the help guys, you're the best :)

Try this is combination (I hope Pat Phelan will not be offended ;) )

select t1.* from #test t1
join (SELECT f1,f2,f3
FROM #test
WHERE f4 IN (1,2,3) -- store list goes here
GROUP BY f1, f2, f3
HAVING Count(DISTINCT f4) = 3 -- store count goes here
) as t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3|||Off we go, into... Nevermind!SELECT *
FROM phrog AS a
WHERE 2 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM phrog AS z
WHERE Id_Cliente IN (1, 10) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)

Edited to fix two logic errors (oops)!

-PatP|||Originally posted by snail
Try this is combination (I hope Pat Phelan will not be offended ;) ) Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.

-PatP|||Originally posted by Pat Phelan
Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.

-PatP Nice to meet you ;)|||Pat Phelan / Snake,

Both your solutions don't work for the following set of data -

Id_Prod_Grupo Id_Prod_Tipo Id_Prod_Pres Id_Cliente
---- ---- ---- ----
0 0 5 1
0 0 5 10
0 0 5 960
0 0 5 15033
0 0 8 1
0 0 8 10
0 0 8 960
0 0 8 92606

The whole table is returned when running the query for 3 stores (1, 10 and 960), whereas row 4 and 8 shouldn't be in the results...|||Did you change the 2 count to a 3 count in my query?

-PatP|||Yes, here is the exact query I ran...

SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE 3 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)|||Uff-da! Bone-head alert. I missed an important part. I showed the products that existed in those three stores, without regard to where those products were stored (doh!). For a band-aid fix, you can use:SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE Id_Cliente IN (1, 10, 960)
AND 3 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)while I think about a more elegant fix. Sorry!

-PatP|||Pat Phelan,

You da man! :) Works perfectly. Thanks a lot!|||Just because I like simple solutions, could you also try:SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE Id_Cliente IN (SELECT Id_Cliente
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres
GROUP BY z.Id_Cliente
HAVING Count(DISTINCT z.Id_Cliente) = 3)) -- store count goes hereThis shouldn't change the execution plan, but it does simplify the query because you only need to include the store list once and store count once in this query.

-PatP|||Originally posted by Pat
Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.
Sounds very familiar...Wait a minute, that's my last annual review you're quoting! Where did you get it?|||Originally posted by rdjabarov
Sounds very familiar...Wait a minute, that's my last annual review you're quoting! Where did you get it? You be amazed at the stuff we've got posted on the walls around here! ;)

-PatPsql