to join table - 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)

to join table

Author

Topic

rajajuliana
Starting Member

4 Posts

Posted - 2008-11-07 : 05:03:24

hi.. i got a prob with my script..

i want to join 3 tables to get supplier info. below is the script.

SELECT
a.OrgnName,
c.OrgnId,
b.FirstName,
c.ContactMode,
c.ContactAdd
FROM
Organisation a,
Person b,
PersonContact c
WHERE
a.orgnid = c.orgnid AND
b.personid = c.personid

and this is the output.

OrgnName | OrgnId |FirstName | ContactMode | ContactAdd
JULIE CO. 333-555 JULIE WORKPHONE 12345678
JULIE CO. 333-555 JULIE WORKFAX 87654321
JULIE CO. 333-555 JULIE EMAIL julie@julie.com
MARY GROUP 222-444 MARY WORKPHONE 65432112
MARY GROUP 222-444 MARY WORKFAX 34567543
MARY GROUP 222-444 MARY EMAIL mary@mary.com

but, i don't want output like this because the data is duplicate in the first 3 columns. i manage to solve this problem by using this script (using CASE : row into column) but only from one table (PersonContact)

select orgnid,
min(case when ContactMode = 'WORKPHONE' then ContactAdd else NULL end) as WorkPhone,
min(case when ContactMode = 'WORKFAX' then ContactAdd else NULL end) as WorkFax,
min(case when contactMode = 'EMAIL' then ContactAdd else NULL end) as Email
from PersonContact
group by orgnid

and the output became like this

OrgnId | WorkPhone | WorkFax | Email
333-555 12345678 87654321 julie@julie.com
222-444 65432112 34567543 mary@mary.com

the problem is, i failed to join the 3 tables (Organisation, Person, PersonContact) in that script to get the output as below

OrgnName | OrgId | FirstName | WOrkPhone | WorkFax | Email
JULIE CO. 333-555 JULIE 12345678 87654321 julie@julie.com
MARY GROUP 222-444 MARY 65432112 34567543 mary@mary.com

i hope somebody can help me..
thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 05:26:30

[code]SELECT
a.OrgnName,
a.OrgnId,
tmp.FirstName,
tmp.WorkPhone,
tmp.WorkFax,
tmp.Email
FROM
Organisation a
INNER JOIN (select c.orgnid,c.FirstName,
min(case when c.ContactMode = 'WORKPHONE' then c.ContactAdd else NULL end) as WorkPhone,
min(case when c.ContactMode = 'WORKFAX' then c.ContactAdd else NULL end) as WorkFax,
min(case when c.contactMode = 'EMAIL' then c.ContactAdd else NULL end) as Email
from PersonContact c
INNER JOIN Person b
ON b.personid = c.personid
group by c.orgnid,c.FirstName
)tmp
ON a.orgnid = tmp.orgnid[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources