Complicated 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

SQL Server 2000 Forums

Transact-SQL (2000)

Complicated Query

Author

Topic

vmahesh
Starting Member

19 Posts

Posted - 2004-08-11 : 10:21:20

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.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-08-11 : 10:26:28

try this


SELECT Table1.ID, Table1.LastName, Table1.FirstName, Email.Value AS Email, Phone.Value AS Phone
FROM Table1 LEFT OUTER JOIN Communication Email ON (Table1.ID = Email.ID) LEFT OUTER JOIN Communication Phone ON (Table1.ID = Phone.ID)

Dustin Michaels

vmahesh
Starting Member

19 Posts

Posted - 2004-08-11 : 10:34:50

This query is giving multiple records for one user name. The result should contain only one record per user name.

Thanks,
VMRao

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-08-11 : 10:40:42

may have to use sub select queries then.


SELECT Table1.ID, Table1.LastName, Table1.FirstName, 
(SELECT Table2.Value FROM Table2 WHERE Table2.ID = Table1.ID AND Table2.Communication = 'Email') AS Email,
(SELECT Table2.Value FROM Table2 WHERE Table2.ID = Table1.ID AND Table2.Communication = 'Phone') AS Phone,
FROM Table1

ffoiii
Starting Member

12 Posts

Posted - 2004-08-11 : 14:58:41

SELECT Table1.ID, Table1.LastName, Table1.FirstName, Email.Value AS Email, Phone.Value AS Phone
FROM Table1 LEFT OUTER JOIN Communication Email ON (Table1.ID = Email.ID and Email.Communication = 'Email')
LEFT OUTER JOIN Communication Phone ON (Table1.ID = Phone.ID and Phone.Communication = 'Phone')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources