Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    10

    Unanswered: Simple SQL query help.

    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!

    P.S. The server is MS SQL Server, just in case.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    PHP Code:
    SELECT
       Id_Prod_Grupo
    ,  Id_Prod_Tipo
    ,  Id_Prod_Pres 
       FROM phrog
       WHERE  Id_Cliente IN 
    (110)  -- store list goes here
       GROUP BY    Id_Prod_Grupo
    Id_Prod_TipoId_Prod_Pres
       HAVING Count
    (DISTINCT Id_Cliente) = -- store count goes here 
    at least as my first shot.

    -PatP

Posting Permissions

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