help me to form the query - 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

General SQL Server Forums

New to SQL Server Programming

help me to form the query

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-01-04 : 02:59:04

i am having 1 mapping table and two look up tables.
mapping table -> map1
lookuptables --> layTable, CPDtable

the mapping table referring laytable's PK and CPDtable's PK.the scenario is like map1 table referring 5 times of laytable's PK for single record.

eg)
create table map1
(
ID number,
Layid1 number,
layid2 number,
layid3 number,
layid4 number,
layid5 number,
CPDID number
)

create table LayTable
(
LayID number,
SourceName varchar2(100)
)

create table CPDTable
(
cpdid number,
cpdName Varchar2(100)
)

insert into laytable
select 1,'phone1' from dual
union all
select 2,'phone2' from dual
union all
select 3,'phone3' from dual
union all
select 4,'phone4' from dual
union all
select 5,'phone5' from dual

insert into map1
select 1,1,2,3,4,5,1 from dual

insert into cpdtable
select 1,'phone' from dual

i formed a query like

select c.cpdname as CPDname ,case when m.layid1=l.layid then sourcename END as SC1 ,case when m.layid2=l.layid then sourcename END as SC2,
,case when m.layid3=l.layid then sourcename END as SC3
,case when m.layid4=l.layid then sourcename END as SC4
,case when m.layid5=l.layid then sourcename END as SC5 from map1 m,laytable L,cpdtable C
where (m.layid1=L.layid
or m.layid2=l.layid
or m.layid3=l.layid
or m.layid4=l.layid
or m.layid5=l.layid
) and m.CPDID=C.CPDID(+)

i am getting output as

cpdname sc1 sc2 sc3 sc4 sc5
phone phone1
phone phone2
phone phone3
.....

but i am expecting

cpdname sc1 sc2 sc3 sc4 sc5
phone phone1 phone2 phone3 phone4 phone5

how to form a query
please help me

thanks in advance
subha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 06:02:54

[code]
select c.cpdName,
lt1.SourceName AS [sc1],
lt2.SourceName AS [sc2],
lt3.SourceName AS [sc3],
lt4.SourceName AS [sc4],
lt5.SourceName AS [sc5]
from map1 m
inner join laytable lt1
on lt1.LayID = m.Layid1
inner join laytable lt2
on lt2.LayID = m.Layid2
inner join laytable lt3
on lt3.LayID = m.Layid3
inner join laytable lt4
on lt4.LayID = m.Layid4
inner join laytable lt5
on lt5.LayID = m.Layid5
inner join CPDTable c
ON c.cpdid = m.cpdid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-01-04 : 06:28:44

Its working thanks friend.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2012-01-04 : 07:09:37

also

select
c.cpdname
,max(case when m.Layid1=l.layid then l.SourceName end) as sc1
,max(case when m.Layid2=l.layid then l.SourceName end) as sc2
,max(case when m.Layid3=l.layid then l.SourceName end) as sc3
,max(case when m.Layid4=l.layid then l.SourceName end) as sc4
,max(case when m.Layid5=l.layid then l.SourceName end) as sc5
from map1 m
inner join cpdtable c on c.cpdid=m.cpdid
left join laytable l on 1=1
group by cpdname

--Ranjit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 12:21:58

quote:
Originally posted by Ranjit.ileni

also

select
c.cpdname
,max(case when m.Layid1=l.layid then l.SourceName end) as sc1
,max(case when m.Layid2=l.layid then l.SourceName end) as sc2
,max(case when m.Layid3=l.layid then l.SourceName end) as sc3
,max(case when m.Layid4=l.layid then l.SourceName end) as sc4
,max(case when m.Layid5=l.layid then l.SourceName end) as sc5
from map1 m
inner join cpdtable c on c.cpdid=m.cpdid
left join laytable l on 1=1
group by cpdname

--Ranjit

why do you need an inner join on trivial condition which ie effectively a cross join?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources