extracting data from 2 tables using join and union

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)

extracting data from 2 tables using join and union

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2015-02-18 : 08:32:16

Hi, I have the below scenario

create table #comp
(
compid int,
compname1 varchar(100),
compname2 varchar(100)
)

insert into #comp values(001,'abc','abc')
insert into #comp values(002,'xyz','efg')
insert into #comp values(003,'lmn',null)
insert into #comp values(004,null,'pqr')
insert into #comp values(005,null,null)

create table #installs
(compid int,
product varchar(100))

insert into #installs values(001,'Adobe')
insert into #installs values(002,'Excel')
insert into #installs values(003,'Word')
insert into #installs values(004,'Powerpoint')
insert into #installs values(005,'SQLSERVER')

-- USED QUERY - NOT GIVING SATISFACTORY RESULT
select distinct c.compid,c.compname1,i.product
from #comp c
join #installs i on c.compid = i.compid
union
select distinct c.compid,c.compname2,i.product
from #comp c
join #installs i on c.compid = i.compid

-- ABOVE QUERY RESULT

1 abc Adobe
2 efg Excel
2 xyz Excel
3 NULL Word
3 lmn Word
4 NULL Powerpoint
4 pqr Powerpoint
5 NULL SQLSERVER

-- DESIRED RESULT SHOULD BE
1 abc Adobe
2 efg Excel
2 xyz Excel
3 lmn Word
4 pqr Powerpoint
5 NULL SQLSERVER

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 09:57:11

Why do you want to see NULL for SQLSERVER but not for Word or Powerpoint?

sachingovekar
Posting Yak Master

101 Posts

Posted - 2015-03-04 : 10:10:38

thats because word and powerpoint have a valid comp name....since sql server is not having any comp name we still need to keep that.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-04 : 11:28:12

[code]
WITH Normalized
AS
(
SELECT DISTINCT compid, compname
FROM
(
SELECT compid, compname1, compname2
FROM #comp
) P
UNPIVOT
(
compname FOR Comp IN (compname1, compname2)
) U
)
SELECT I.compid, N.compname, I.product
FROM #installs I
LEFT JOIN Normalized N
ON I.compid = N.compid;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources