grouping a few columns

/*
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 @.tableJ055 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
>
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
--|||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
"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
>
>
>|||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