August 4, 2002 at 11:56 pm
Hi,
I would really appreciate if somebody could tell me an alternative way to find the number of records. I do know that we can use the EXISTS clause, but am not sure of how to handle it...
for eg.
Table T1
IdProduct
1A
1B
1C
2A
2C
3A
3B
3C
3D
4A
4C
4D
5A
5B
5C
5D
how do list the id's who sell all product ( Master list of products should be the unique product found in this table).
This was what I wrote...
Select distinct id1 from k1
group by id1
having count(id1) = (
Select top 1 count(id1) a1 from k1
group by id1 order by a1 desc)
(but this was using the Count function...)
thanks in advance
JK
August 5, 2002 at 12:29 am
Using COUNT(*) as you have is a typical way to get full intersection when you have a structure similar to the way you do. Is there a reason why you can't (don't want to) use it? However, a more typical way to write it is (if the combination of ID and Product is guaranteed to be unique):
SELECT
ID
FROM T1
GROUP BY ID
HAVING COUNT(*) = (
SELECT
COUNT(DISTINCT Product)
FROM T1)
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 5, 2002 at 12:50 am
Hello,
I am really sorry if my requirement was not comprehensive enough...
The answer is 3 & 5, since it contains all the products(A,B,C,D)...
It was out of sheer curiousity that I want to know an alternative solution.
best regards...
JK
JK
August 5, 2002 at 1:50 am
You may take a look at the Transact-SQL Cookbook published by O'Reilly. It's available online through Safari (http://safari.informit.com or http://safari.oreilly.com). Chapter 2 covers the handling of sets, which is applicable to cases such as this. Section 2.6 is this exact case.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply