can this be done?

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,
HowardHoward wrote:
> 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
--|||> 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/|||Hello Kent,
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/sql