Join problem - 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 2005 Forums

Transact-SQL (2005)

Join problem

Author

Topic

Stig Christian Aske
Starting Member

6 Posts

Posted - 2008-07-12 : 07:03:46

Hi!

My Left Table looks like this:

CustomerID
CustomerCompanyName
CustomerVisitAddressLine1
CustomerVisitAddressLine2
CustomerVisitAddressZipCodeID
CustomerPostalAddressLine1
CustomerPostalAddressLine2
CustomerPostalAddressZipCodeID

... and my right table ...

ZipCodeID
Place

So my problem is that I have two different ZipCodeID's for each record in the left table.

How can I get the values (twice) from the right table?

Thanks!

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-12 : 07:50:22

There must be a wrinkle to this that I'm missing. Are you saying you want only 1 place to return for both values in the right table? Or just do a straightforward return whatever matches in the right table. Example below if the latter is the case. Otherwise I need you more explanation. Maybe post what you want your result to look like.
select l.CustomerId,r.Place
from LeftTable L
inner join RightTable r on l.CustomerPostalAddressZipCodeID=r.ZipCodeID

Mike
"oh, that monkey is going to pay"

Stig Christian Aske
Starting Member

6 Posts

Posted - 2008-07-12 : 08:00:51

Hi!

I want the result to look like this:

CustomerCompanyName CustomerVisitAddressZipCodeID Place CustomerPostalAddressZipCodeID Place

ex. MixMedia INC, South Beach 1, Seattle, PO. Box 55, Los Angeles

So I want to do two queries from the right table for each record in the left table. First the VisitAddressZipCodeID, and then the PostalAddressZipCodeID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-12 : 08:23:16

Join it twice to right table

SELECT t1.CustomerCompanyName, t1.CustomerVisitAddressZipCodeID, vadd.Place, t1.CustomerPostalAddressZipCodeID, padd.Place
FROM LeftTable t1
INNER JOIN RightTable vadd
ON vadd.ZipCodeID=t1.CustomerVisitAddressZipCodeID
INNER JOIN RightTable padd
padd.ZipCodeID=t1.CustomerPostalAddressZipCodeID

Stig Christian Aske
Starting Member

6 Posts

Posted - 2008-07-12 : 08:28:58

Thanks! That works perfectly!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-12 : 08:34:49

Don't forget the ON keyword after padd alias.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources