Query by joining lookup and onetomany table

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

Transact-SQL (2008)

Query by joining lookup and onetomany table

Author

Topic

nbalraj
Starting Member

21 Posts

Posted - 2014-01-22 : 11:34:34

Hi,

Can you please help with the below scenario:

opp table (primary table)
oppid opp title
1 test opp
2 test opp2
3 test opp3

bunit table (one to many table)
oppid bunitid
1 1
1 2
1 4
2 5
2 1
3 3
3 4

dunit table (one to many table)
oppid dunitid
1 1
1 2
2 1
3 3
3 1

lookup table
codeid classification description
1 bunit GED
2 bunit GLC
3 bunit CCC
4 bunit DDD
5 bunit SSS
1 dunit iii
2 dunit kkk
3 dunit nnn

Final output, we need

oppid title bunit dunit
1 test opp GED,GLC,DDD iii,kkk
2 test opp2 SSS,GED iii,nnn
3 test opp3 CCC,DDD nnn,iii

Thanks for any help and directions.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-01-22 : 14:57:01

It took me a while to figure out the ER, but I think this query works:



with opp(oppid, opp_title) as (
	select * from (values
		(1, 'test opp'),
		(2, 'test opp2'),
		(3, 'test opp3')
		) opp(oppid, opp_title)
	),

	bunit (oppid, bunitid) as (
	select * from (values
		(1, 1),
		(1, 2),
		(1, 4),
		(2, 5),
		(2, 1),
		(3, 3),
		(3, 4)
		) bunit (oppid, bunitid)
	),
	
	dunit(oppid, dunitid) as (
		select * from (values
			(1, 1),
			(1, 2),
			(2, 1),
			(3, 3),
			(3, 1)
			) dunit(oppid, dunitid)
	),
			
	lookup_table(codeid, class, descr) as (
		select * from (values
			(1, 'bunit', 'GED'),
			(2, 'bunit', 'GLC'),
			(3, 'bunit', 'CCC'),
			(4, 'bunit', 'DDD'),
			(5, 'bunit', 'SSS'),
			(1, 'dunit', 'iii'),
			(2, 'dunit', 'kkk'),
			(3, 'dunit', 'nnn')
			) lookup_table(codeid, class, descr) 
	),
	
	codes (oppid, descr) as (
		select b.oppid oppid, lt.descr from bunit b
		join lookup_table lt on b.bunitid = lt.codeid and lt.class = 'bunit'
		
		union
		
		select d.oppid, lt.descr from dunit d
		join lookup_table lt on d.dunitid = lt.codeid and lt.class = 'dunit'
	)
	
	
select opp.oppid, opp.opp_title, left(codes.descr, LEN(codes.descr) -1) as 'bunit dunit'
from opp
cross apply (
	select codes.descr + ','
	from codes
	where codes.oppid = opp.oppid
	for XML path('')
	) codes(descr)

I get these results:


oppid       opp_title bunit dunit
----------- --------- -----------
1           test opp  DDD,GED,GLC,iii,kkk
2           test opp2 GED,iii,SSS
3           test opp3 CCC,DDD,iii,nnn

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-01-23 : 07:22:49

;with cte (oppid,bunitid,description)
as
(
select b.oppid,b.bunitid,l.description from @bunit as b
inner join @lookup as l
on b.bunitid = l.codeid
where l.classification = 'bunit'
),cte1(oppid,dunitid,description)
as
(
select d.oppid,d.dunitid,l.description from @dunit as d
inner join @lookup as l
on d.dunitid = l.codeid
where l.classification = 'dunit'
)
select distinct
op.opp
, op.oppid
, op.title
, left(cte.description, LEN(cte.description) -0) as 'bunit'
, left(cte1.description, LEN(cte1.description) -0) as 'dunit'
from @opp as op
cross apply
(select ','+ c.description
from cte as c
where c.oppid = op.oppid
for XML path('')
)cte(description)
cross apply
(select ','+c.description
from cte1 as c
where c.oppid = op.oppid
for XML path('')
)cte1(description)

Veera

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 07:23:26

just use a single cte instead of nested ones


;With CTE
AS
(
SELECT o.oppid, o.opptitle,b.bunitid,lb.description  as [bunit],d.dunitid,ld.description as [dunit]
FROM opp o
LEFT JOIN bunit b
ON b.oppid = o.oppid 
LEFT JOIN lookup lb
ON lb.codeid = b.bunitid
AND lb.classification = 'bunit'
LEFT JOIN dunit d
ON d.oppid = o.oppid 
LEFT JOIN lookup ld
ON ld.codeid = d.dunitid
AND ld.classification = 'dunit'
)

SELECT oppid,
opptitle,
STUFF((SELECT ',' + bunit
       FROM CTE
       WHERE oppid = c.oppid
       AND bunit IS NOT NULL
       ORDER BY bunitid
       FOR XML PATH('')),1,1,'') AS bunit,
 STUFF((SELECT ',' + dunit
       FROM CTE
       WHERE oppid = c.oppid
       AND dunit IS NOT NULL
       ORDER BY dunitid
       FOR XML PATH('')),1,1,'') AS dunit
FROM (SELECT DISTINCT oppid,opptitle FROM CTE) c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-01-23 : 08:52:57

quote:
Originally posted by visakh16

just use a single cte instead of nested ones

...snip

that doesn't work correctly, I think. When I run your version I get:


oppid       opp_title bunit                  dunit
----------- --------- ---------------------- ------------------------
1           test opp  GED,GED,GLC,GLC,DDD,DDD iii,iii,iii,kkk,kkk,kkk
2           test opp2 GED,SSS                iii,iii
3           test opp3 CCC,CCC,DDD,DDD

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 13:42:09

that can be dealt with by a simple tweak
see below


declare @opp table 
(
oppid int,
opp varchar(30),
title varchar(10)
)
insert @opp
values(1,'test','opp'),
(2,'test','opp2'),
(3,'test','opp3')


declare @bunit table 

(
oppid int,
bunitid int
)
insert @bunit
values(1,	1),
(1,	2),
(1,	4),
(2,	5),
(2,	1),
(3,	3),
(3,	4)

declare @dunit table 
(
oppid int,
 dunitid int
)
insert @dunit
values(1,	1),
(1,	2),
(2,	1),
(3,	3),
(3,	1)


declare @lookup table
(
codeid int,
classification varchar(50),
[description] varchar(50)
)
insert @lookup
values(1,'bunit','GED'),
(2,'bunit','GLC'),
(3,'bunit','CCC'),
(4,'bunit','DDD'),
(5,'bunit','SSS'),
(1,'dunit','iii'),
(2,'dunit','kkk'),
(3,'dunit','nnn')


;With CTE
AS
(
SELECT o.oppid, o.opp,title,b.bunitid,lb.description  as [bunit],d.dunitid,ld.description as [dunit]
FROM @opp o
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY oppid ORDER BY bunitid) AS Rn,*
           FROM @bunit) b
ON b.oppid = o.oppid 
LEFT JOIN @lookup lb
ON lb.codeid = b.bunitid
AND lb.classification = 'bunit'
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY oppid ORDER BY dunitid) AS Rn,*
           FROM @dunit) d
ON d.oppid = o.oppid 
AND d.Rn = b.Rn
LEFT JOIN @lookup ld
ON ld.codeid = d.dunitid
AND ld.classification = 'dunit'
)

SELECT oppid,
opp,title,
STUFF((SELECT  ',' + bunit
       FROM CTE
       WHERE oppid = c.oppid
       AND bunit IS NOT NULL
       ORDER BY bunitid
       FOR XML PATH('')),1,1,'') AS bunit,
 STUFF((SELECT  ',' + dunit
       FROM CTE
       WHERE oppid = c.oppid
       AND dunit IS NOT NULL
       ORDER BY dunitid
       FOR XML PATH('')),1,1,'') AS dunit
FROM (SELECT DISTINCT oppid,opp,title FROM CTE) c


output
-------------------------------------------------------
oppid	opp	title	bunit	        dunit
-------------------------------------------------------
1	test	opp	GED,GLC,DDD	iii,kkk
2	test	opp2	GED,SSS	        iii
3	test	opp3	CCC,DDD	        iii,nnn

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources