Convoluted join - 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)

Convoluted join

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-03-21 : 15:00:21

Hi,

I would appreciate help with the following:

declare @a table (id int, stat varchar(4), cons char(1))
insert	@a
select	1, 'PENY', 'C' union all
select	2, 'REGS', 'N' union all
select	3, NULL,   '-' union all
select	4, 'DENT', 'C' union all
select	5, 'WASO', 'C'

declare @stoff table (id int, typ int, nme varchar(10), rec char(1), rec_id int, reg int)
insert @stoff
select	1, 1, 'nme 1',  'Y', 4802, 3510 union all
select	1, 4, 'nme 1',  'Y', 4802, NULL union all
select	2, 1, 'nme 2',  'N', 0,    3510 union all
select	2, 4, 'nme 2a', 'N', 0,    3510 union all
select	3, 1, 'nme 3',  'N', 0,    3510 union all
select	4, 1, 'nme 4',  'E', 4833, 3510 union all
select	4, 4, 'nme 4a', 'E', 4833, 3510 union all
select	4, 5, NULL,     'E', 4833, NULL union all
select	5, 1, NULL,     'Y', 4838, 3510 union all
select	5, 4, 'nme 5a', 'Y', 4838, NULL union all
select	6, 1, 'nme 6',  'N', 0,    4911

declare @acon table (id int, cde varchar(4), dte smalldatetime)
insert	@acon
select	1, 'DENT', '11/30/2005' union all
select	1, 'DRUM', '10/02/2004' union all
select	1, 'STUL', '01/27/2006' union all
select	2, 'CRUM', '03/06/1998' union all
select	2, 'BUGS', '03/07/1998' union all
select	3, 'AORG', '05/03/2004' union all
select	5, 'MORK', '04/12/1991'

I have one record per id in @a, between 1 and 3 in @stoff and 0 or more in @acon. All these tables can be joined on id.

What I need to do is:
a) pull list of distinct ids from @stoff, where rec_id=3510. This will be number of records I'm expecting to return (here: 5).
b) for each id, get stat and cons from @a
c) for each id, get cde and dte from @acon, where dte is latest for particular id
d) for each id get rec_id and rec from @stoff; these values will be the same for all the records in @stoff with the same id
e) for each id get nme from @stoff where typ=5; if this is null, nme from @stoff where typ=4; if this is null, nme from @stoff where typ=1

In this case I'm expecting:


id stat cons dte        cde  rec_id rec nme
----------------------------------------------
1  PENY C    01/27/2006 STUL 4802   Y   nme 1
2  REGS N    03/07/1998 BUGS 0      N   nme 2a
3  NULL -    05/03/2004 AORG 0      N   nme 3
4  DENT C    NULL       NULL 4833   E   nme 4a
5  WASO C    04/12/1992 MORK 4838   Y   nme 5a

I can do all these things one at a time (and I learned it all here, too

), but I can't seem to put it together in one query.

Any help appreciated...

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-03-21 : 15:20:34

Here's what I have so far:

select a.id, a.stat, a.cons, action.dte, action.cde, inst.rec_id, inst.rec
from @a a
left join @acon action
	inner join (
	select id, max(dte) dte from @acon group by id having id in (
		select distinct stoff.id from @stoff stoff where stoff.reg=3510)
	) as max_action
	on action.id=max_action.id
on action.id=a.id and action.dte=max_action.dte
left join (
	select distinct id, rec, rec_id from @stoff 
	where id in (select distinct stoff.id from @stoff stoff where stoff.reg=3510)
	) as inst
on inst.id=a.id
where a.id in (select distinct stoff.id from @stoff stoff where stoff.reg=3510)

Should I include parts in blue, or not? Does it make a difference to make sure derived tables are as small as possible, or not? This query will be ran against large tables.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 16:04:46

BTW, the output is not consistent with sample data. The year for ID 5 is one year off...

-- Prepare sample data
declare @a table (id int, stat varchar(4), cons char(1))

insert	@a
select	1, 'PENY', 'C' union all
select	2, 'REGS', 'N' union all
select	3, NULL,   '-' union all
select	4, 'DENT', 'C' union all
select	5, 'WASO', 'C'

declare @stoff table (id int, typ int, nme varchar(10), rec char(1), rec_id int, reg int)

insert	@stoff
select	1, 1, 'nme 1',  'Y', 4802, 3510 union all
select	1, 4, 'nme 1',  'Y', 4802, NULL union all
select	2, 1, 'nme 2',  'N', 0,    3510 union all
select	2, 4, 'nme 2a', 'N', 0,    3510 union all
select	3, 1, 'nme 3',  'N', 0,    3510 union all
select	4, 1, 'nme 4',  'E', 4833, 3510 union all
select	4, 4, 'nme 4a', 'E', 4833, 3510 union all
select	4, 5, NULL,     'E', 4833, NULL union all
select	5, 1, NULL,     'Y', 4838, 3510 union all
select	5, 4, 'nme 5a', 'Y', 4838, NULL union all
select	6, 1, 'nme 6',  'N', 0,    4911

declare	@acon table (id int, cde varchar(4), dte smalldatetime)
insert	@acon
select	1, 'DENT', '11/30/2005' union all
select	1, 'DRUM', '10/02/2004' union all
select	1, 'STUL', '01/27/2006' union all
select	2, 'CRUM', '03/06/1998' union all
select	2, 'BUGS', '03/07/1998' union all
select	3, 'AORG', '05/03/2004' union all
select	5, 'MORK', '04/12/1991'

-- Show the expected output
SELECT		s.ID,
		a.Stat,
		a.Cons,
		c.dte,
		c.cde,
		s.Rec_ID,
		s.Rec,
		s.Nme,
		s.Typ
FROM		@Stoff AS s
LEFT JOIN	@A AS a ON a.ID = s.ID
LEFT JOIN	@Acon AS c ON c.ID = a.ID
WHERE		s.Reg = 3510
		AND (c.dte = (SELECT TOP 1 x.dte FROM @Acon AS x WHERE x.ID = c.ID ORDER BY x.dte DESC) OR c.DTE IS NULL)
		AND s.Typ = ISNULL((SELECT TOP 1 e.Typ FROM @Stoff AS e WHERE e.ID = s.ID AND e.Nme IS NOT NULL AND e.Reg = 3510 ORDER BY e.Typ DESC), s.Typ)

Peter Larsson
Helsingborg, Sweden

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-03-23 : 09:20:10

Thanks!

I was looking at this in a very different way, and my query ended up being much longer (as you can gather from what I posted earlier).

You retrieved one set of records and filtered away what you didn't need, and I got set of records from one table and kept adding columns to it.

Thanks again - it's very helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources