Smart, multiple rows based query

6 Apr 2006 5:28 PM

Oscar

I want to find a sql query for the following case :

ID    ID_client    ID_prodnr
   1    2                6
   2    2                5
   3    1                3
   4    2                8

How can I write one sql query for the case in which I want to verify whether
ID_client 2 has disposal of the three ID_prodnr , such as 5,6,and 8 rather
than verify each ID_prodnr value with an individual query?
So the query should verify whether there are 3 records for ID_client=2 which
hold the values of all three ID_prodnr 5,6 and 8 ? Can I accomplish this
with only one query?

regards,
Oscar

6 Apr 2006 5:55 PM

Edgardo Valdez, MCSD, MCDBA

can you give an example of the expected output?

Show quote

"Oscar" wrote:

> I want to find a sql query for the following case :
>
> ID    ID_client    ID_prodnr
>    1    2                6
>    2    2                5
>    3    1                3
>    4    2                8
>
> How can I write one sql query for the case in which I want to verify whether
> ID_client 2 has disposal of the three ID_prodnr , such as 5,6,and 8 rather
> than verify each ID_prodnr value with an individual query?
> So the query should verify whether there are 3 records for ID_client=2 which
> hold the values of all three ID_prodnr 5,6 and 8 ? Can I accomplish this
> with only one query?
>
> regards,
> Oscar
>
>
>

6 Apr 2006 8:39 PM

Oscar

Hi Edgardo,

the expected output should be only one record with one field which holds the
ID_client.

Oscar

"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMC***@discussions.microsoft.com> schreef in bericht
Show quote

news:9C84DC82-4A17-4FD9-9808-5D95A91D1397@microsoft.com...
> can you give an example of the expected output?
>
>
> "Oscar" wrote:
>
>> I want to find a sql query for the following case :
>>
>> ID    ID_client    ID_prodnr
>>    1    2                6
>>    2    2                5
>>    3    1                3
>>    4    2                8
>>
>> How can I write one sql query for the case in which I want to verify
>> whether
>> ID_client 2 has disposal of the three ID_prodnr , such as 5,6,and 8
>> rather
>> than verify each ID_prodnr value with an individual query?
>> So the query should verify whether there are 3 records for ID_client=2
>> which
>> hold the values of all three ID_prodnr 5,6 and 8 ? Can I accomplish this
>> with only one query?
>>
>> regards,
>> Oscar
>>
>>
>>

6 Apr 2006 9:12 PM

David Portas

Oscar wrote:
Show quote

> I want to find a sql query for the following case :
>
> ID    ID_client    ID_prodnr
>    1    2                6
>    2    2                5
>    3    1                3
>    4    2                8
>
> How can I write one sql query for the case in which I want to verify whether
> ID_client 2 has disposal of the three ID_prodnr , such as 5,6,and 8 rather
> than verify each ID_prodnr value with an individual query?
> So the query should verify whether there are 3 records for ID_client=2 which
> hold the values of all three ID_prodnr 5,6 and 8 ? Can I accomplish this
> with only one query?
>
> regards,
> Oscar

Please include DDL in future so that we don't have to guess your keys,
constraints, etc.

If (id_client, id_prodnr) is unique:

SELECT id_client
FROM your_table
WHERE id_prodnr IN (5,6,8)
GROUP BY id_client
HAVING COUNT(*)= 3;

Otherwise:

SELECT id_client
FROM your_table
WHERE id_prodnr IN (5,6,8)
GROUP BY id_client
HAVING COUNT(DISTINCT id_prodnr)= 3;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

7 Apr 2006 9:44 AM

Oscar

Thanks David,

Your advise was exactly what I was looking for. Problem solved.

regards,
Oscar

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> schreef in bericht
news:1144357969.519750.35260@j33g2000cwa.googlegroups.com...
> Oscar wrote:
>> I want to find a sql query for the following case :
>>
>> ID    ID_client    ID_prodnr
>>    1    2                6
>>    2    2                5
>>    3    1                3
>>    4    2                8
>>
>> How can I write one sql query for the case in which I want to verify
>> whether
>> ID_client 2 has disposal of the three ID_prodnr , such as 5,6,and 8
>> rather
>> than verify each ID_prodnr value with an individual query?
>> So the query should verify whether there are 3 records for ID_client=2
>> which
>> hold the values of all three ID_prodnr 5,6 and 8 ? Can I accomplish this
>> with only one query?
>>
>> regards,
>> Oscar
>
> Please include DDL in future so that we don't have to guess your keys,
> constraints, etc.
>
> If (id_client, id_prodnr) is unique:
>
> SELECT id_client
> FROM your_table
> WHERE id_prodnr IN (5,6,8)
> GROUP BY id_client
> HAVING COUNT(*)= 3;
>
> Otherwise:
>
> SELECT id_client
> FROM your_table
> WHERE id_prodnr IN (5,6,8)
> GROUP BY id_client
> HAVING COUNT(DISTINCT id_prodnr)= 3;
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>