can this be done?

18 Mar 2006 8:13 PM

Howard

Table1
ID|CATID|NAME
1    3              A
2    3              B
3    3              C
4    4              D
5    4              E

I want to write a query that pull all the record from the same CATID but
with only ID given.

If ID = 4 it would return ID 4 and 5 because they are in the same category
If ID = 2 it would return ID 1,2,3

Thanks,
Howard

18 Mar 2006 9:11 PM

David Portas

Howard wrote:
Show quote

> Table1
> ID|CATID|NAME
> 1    3              A
> 2    3              B
> 3    3              C
> 4    4              D
> 5    4              E
>
> I want to write a query that pull all the record from the same CATID but
> with only ID given.
>
> If ID = 4 it would return ID 4 and 5 because they are in the same category
> If ID = 2 it would return ID 1,2,3
>
> Thanks,
> Howard

DECLARE @id INT;

SET @id = 4;

SELECT id
FROM table1
WHERE catid =
(SELECT catid
FROM table1
WHERE id = @id);

--
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
--

18 Mar 2006 11:37 PM

Kent Tegels

> I want to write a query that pull all the record from the same CATID
> but with only ID given.
> If ID = 4 it would return ID 4 and 5 because they are in the same
> category If ID = 2 it would return ID 1,2,3

A relatively easy way to do that would be using in sub-query:

create table dbo.table1(id tinyint,catid tinyint,name char(1))
go
insert into dbo.table1(id,catid,name)
select 1,3,'A' union all
select 2,3,'B' union all
select 3,3,'C' union all
select 4,4,'D' union all
select 5,4,'E'
go

declare @id tinyint
set @id = 1
select * from dbo.table1
where catid in
(select catid from dbo.table1 where id=@id)
go

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

18 Mar 2006 11:55 PM

Kent Tegels

Hello Kent,

>> I want to write a query that pull all the record from the same CATID
>> but with only ID given.
>> If ID = 4 it would return ID 4 and 5 because they are in the same
>> category If ID = 2 it would return ID 1,2,3

declare @id tinyint
set @id = 4
select t2.* from dbo.table1 t1
left join dbo.table1 t2
on t1.catid = t2.catid
where t1.id = @id

is an option too.

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/