Complicated Query
-
Hi,
I have two tables (table1 , table2) with a matching "id" between them. 'id' is primary key in table1.
Here is the sample data between them.
Table1
======
ID LASTNAME FIRSTNAME
1 Smith John
2 Mathew Robert
3 Thomas David
Table2
======
ID COMMUNICATION VALUE
1 Phone 123-456-7890
1 Email john@mail.com
2 Email robert@email.com
3 Phone 135-246-7890
I have to combine these tables and get the following result.
Result
======
ID LastName FirstName Email Phone
1 Smith John john@mail.com 123-456-7890
2 Mathew Robert robert@email.com
3 Thomas David 135-246-7890
Is it possible to come up with a query to produce the above result ?
Thanks for your help,
VMRao.
-
select
t1.ID,
t1.LastName,
t1.FirstName,
m.VALUE as Email,
p.VALUE as Phone
from
Table1 t1
left outer join
Table2 m on t1.ID = m.ID and m.COMMUNICATION = 'Email'
left outer join
Table2 p on t1.ID = p.ID and m.COMMUNICATION = 'Phone'
Stephen Marais
Integration Architect
Digiata Technologies
www.digiata.com -
Or like this -
select t1.id, t1.lastname, t1.firstname,
case t2.communication
when 'phone' then t2.value else null end as phone,
case t2.communication
when 'email' then t2.value else null end as email
from table1 t1, table2 t2
where t1.id *= t2.id
------------------------------
The Users are always right - when I'm not wrong! -
Try this
select
distinct TABLE1.ID ID,TABLE1.FIRESTNAME FIRSTNAME,LASTNAME,P.VALUE "PHONE",Q.VALUE "MAIL"
FROM
TABLE1 ,TABLE2 ,
(select x.ID ID,y.VALUE Value
from table1 x,table2 y
where x.ID=y.Id
and y.COMMUNICATION='Phone') P,
( select x.ID ID,y.VALUE value
from table1 x,table2 y
where x.ID=y.Id
and y.COMMUNICATION='Email')Q
where
table1.id=table2.id
and
table1.id=P.id(+)
and
table2.id=Q.id(+)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply