Self-joining a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Self-joining a table

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-15 : 17:56:26

I have two tables:

declare @a table (id int, sth char(3))
insert	@a
select	1, 'ABC' union all
select	2, 'ADG' union all
select	3, 'AFJ' union all
select	4, 'QET' union all
select	5, 'QRU'

declare @b table (id int, nme varchar(6), dt datetime, cde char(1))
insert	@b
select	1, 'AV209', '10/11/2004', 'P' union all
select	2, 'CI101', '03/28/2001', 'P' union all
select	2, 'GH331', '08/01/2004', 'R' union all
select	3, 'AT202', '10/09/2006', 'V'

They can be joined on id (inner join). I need to retrieve the following fields:

select 
	a.sth
,	b.nme
,	(b.dt where cde='P') as dteA
,	(b.dt where cde='R') as dteB
from @a as a
inner join @b as b on a.id=b.id

The dteA and dteB are dates from records with the same id as the one retrieved, where cde is as indicated. So I thought I need to self-join table b two times (as c and d for example, narrowing the selection with WHERE), but MSSQL is complaining whatever I try. How do I do it?
I should add that some ids may not have both of these records (cde='P' and cde='R') - in which case I need to place NULLs in the output field(s).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 18:27:21

Without your supplied output based on the sample data you have provided, all we can give are educated guesses.

declare @a table (id int, sth char(3))
insert	@a
select	1, 'ABC' union all
select	2, 'ADG' union all
select	3, 'AFJ' union all
select	4, 'QET' union all
select	5, 'QRU'

declare @b table (id int, nme varchar(6), dt datetime, cde char(1))
insert	@b
select	1, 'AV209', '10/11/2004', 'P' union all
select	2, 'CI101', '03/28/2001', 'P' union all
select	2, 'GH331', '08/01/2004', 'R' union all
select	3, 'AT202', '10/09/2006', 'V'


select		a.sth,
		b.nme,
		case when cde = 'P' then b.dt end as dteA,
		case when cde = 'R' then b.dt end as dteB
from		@a as a
inner join	@b as b on b.id = a.id

Peter Larsson
Helsingborg, Sweden

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-16 : 08:12:00

quote:
Originally posted by Peso

Without your supplied output based on the sample data you have provided, all we can give are educated guesses.

Sorry for ambiguous question. What I'm after is result like:

sth     nme	dteA        dteB
ABC     AV209	10/11/2004  NULL
ADG	CI101	03/28/2001  08/01/2004
ADG	GH331	03/28/2001  08/01/2004
AFJ	AT202	NULL        NULL

For a particular id, the first two fields are what they are, and the others are b.dt where b.cde='P' (dteA) or b.cde='R' (dteB) - for this id.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 08:33:08

[code]-- prepare sample data
declare @a table (id int, sth char(3))
insert @a
select 1, 'ABC' union all
select 2, 'ADG' union all
select 3, 'AFJ' union all
select 4, 'QET' union all
select 5, 'QRU'

declare @b table (id int, nme varchar(6), dt datetime, cde char(1))
insert @b
select 1, 'AV209', '10/11/2004', 'P' union all
select 2, 'CI101', '03/28/2001', 'P' union all
select 2, 'GH331', '08/01/2004', 'R' union all
select 3, 'AT202', '10/09/2006', 'V'

-- show the output
select q.sth,
q.nme,
d.dteA,
d.dteB
from (
select id,
max(case when cde = 'p' then dt end) as dteA,
max(case when cde = 'r' then dt end) as dteB
from @b
group by id
) as d
inner join (
select a.sth,
a.id,
b.nme
from @a as a
inner join @b as b on b.id = a.id
) as q on q.id = d.id[/code]

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-16 : 08:35:30

[code]

select a.sth, b.nme, c.dteA, c.dteB
from @a a inner join @b b
on a.id = b.id
inner join
(
select id,
dteA = max(case when cde = 'P' then dt end),
dteB = max(case when cde = 'R' then dt end)
from @b
group by id
) c
on a.id = c.id
order by a.sth
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-16 : 08:36:39