grouping a few columns

12 Jan 2006 7:55 PM

J055

/*

Hi

I need to query this table to get results where ids are found with every
searchNum, i.e. the results of this would be:

id

--

1

2

because both id 1 and 2 are found with searchNum 1,2,3. The table could be
any size with any variation of ids and searchNum so I need some sort of
general grouping query. Hope this makes sence. I've been bashing my head
against the wall all day.

thanks Andrew

*/

declare @table table (searchNum int, word varchar(50), id int)

insert into @table values (1, 'cambridge', 1)

insert into @table values (1, 'northampton', 2)

insert into @table values (1, 'hull', 4)

insert into @table values (2, 'laboratory', 1)

insert into @table values (2, 'chemistry', 2)

insert into @table values (2, 'chemistry', 5)

insert into @table values (2, 'laboratory', 2)

insert into @table values (2, 'laboratory', 4)

insert into @table values (3, 'scientist', 1)

insert into @table values (3, 'scientist', 2)

select * from @table

12 Jan 2006 8:12 PM

David Portas

J055 wrote:
Show quote

> /*
>
> Hi
>
> I need to query this table to get results where ids are found with every
> searchNum, i.e. the results of this would be:
>
> id
>
> --
>
> 1
>
> 2
>
> because both id 1 and 2 are found with searchNum 1,2,3. The table could be
> any size with any variation of ids and searchNum so I need some sort of
> general grouping query. Hope this makes sence. I've been bashing my head
> against the wall all day.
>
> thanks Andrew
>

Thanks for posting the DDL and sample data. Please do also include keys
and constraints with your DDL. It can make a big difference to the
solution. Here's one suggestion:

SELECT id
FROM @table
GROUP BY id
HAVING COUNT(DISTINCT searchnum)=
(SELECT COUNT(DISTINCT searchnum)
FROM @table);

If searchnum is a foreign key you could also reference the other table:

SELECT id
FROM @table
GROUP BY id
HAVING COUNT(DISTINCT searchnum)=
(SELECT COUNT(*)
FROM search);

--
David Portas
SQL Server MVP
--

12 Jan 2006 8:24 PM

Daniel P.

Try this:

SELECT [id] FROM
(
    SELECT id, COUNT(*) AS NofRecs
    FROM (SELECT DISTINCT searchNum, [id] FROM @table) AS inn
    GROUP BY [ID]
    HAVING COUNT(*) IN
    (
        SELECT COUNT( DISTINCT searchNum ) FROM @table
    )
) AS cnt

Show quote

"J055" wrote:
> /*
>
> Hi
>
> I need to query this table to get results where ids are found with every
> searchNum, i.e. the results of this would be:
>
> id
>
> --
>
> 1
>
> 2
>
> because both id 1 and 2 are found with searchNum 1,2,3. The table could be
> any size with any variation of ids and searchNum so I need some sort of
> general grouping query. Hope this makes sence. I've been bashing my head
> against the wall all day.
>
> thanks Andrew
>
> */
>
> declare @table table (searchNum int, word varchar(50), id int)
>
> insert into @table values (1, 'cambridge', 1)
>
> insert into @table values (1, 'northampton', 2)
>
> insert into @table values (1, 'hull', 4)
>
> insert into @table values (2, 'laboratory', 1)
>
> insert into @table values (2, 'chemistry', 2)
>
> insert into @table values (2, 'chemistry', 5)
>
> insert into @table values (2, 'laboratory', 2)
>
> insert into @table values (2, 'laboratory', 4)
>
> insert into @table values (3, 'scientist', 1)
>
> insert into @table values (3, 'scientist', 2)
>
> select * from @table
>
>
>
>
>

12 Jan 2006 8:41 PM

Anith Sen

This is division, the usual approach is:

SELECT id
FROM ( SELECT id, COUNT( DISTINCT searchnum)
           FROM tbl
          GROUP BY id ) D ( id, num )
WHERE ( SELECT COUNT(DISTINCT searchnum)
           FROM tbl ) = num ;

--
Anith