Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2004
    Posts
    10

    Unanswered: 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!
    Last edited by anujjain; 04-05-04 at 16:03.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Table definitions and some sample data (INSERTs) would be helpful.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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)

  4. #4
    Join Date
    Jan 2004
    Posts
    10
    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?

  5. #5
    Join Date
    Jan 2004
    Posts
    10
    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!!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You didn't like my first solution?

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Pat Phelan
    You didn't like my first solution?
    Nice try - it works better than mine.

  8. #8
    Join Date
    Jan 2004
    Posts
    10
    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..

  9. #9
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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 ...

  10. #10
    Join Date
    Jan 2004
    Posts
    10
    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

  11. #11
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Off we go, into... Nevermind!
    PHP Code:
    SELECT *
       
    FROM phrog AS a
       WHERE  2 
    = (SELECT Count(DISTINCT Id_Cliente)  -- store count goes here
          FROM phrog 
    AS z
          WHERE  Id_Cliente IN 
    (110)  -- 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
    Last edited by Pat Phelan; 04-06-04 at 00:05.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  14. #14
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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

  15. #15
    Join Date
    Jan 2004
    Posts
    10
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •