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.
| Author |
Topic |
|
e5franson
Starting Member
2 Posts |
 Posted - 2009-10-06 : 07:52:09
|
| Hi Im new to this siteIve got a querying questionIve got 3 tables, Customer, Vendor, and ZIPTable(which has corresponding city and state info for each zipcode). Both Customer and Vendor contain a Zip Code. Im trying to create a query that would list the first and last name from both customer and vendor who live in the same state. Im having trouble figuring out how to retreive the state from the ziptable so I can compare the states for each customer and vendor. I thought I had something going with a Where in nested statement, but now im lost. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-06 : 07:58:14
|
No nested query needed.Just join what you need.Join your Ziptable on Customer and also join your Ziptable a second time with another alias on Vendor.Then you can compare the states.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
e5franson
Starting Member
2 Posts |
Posted - 2009-10-06 : 08:00:44
|
| Thanks for the response. Ive never used join before, how would I go about doing that? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-06 : 08:12:36
|
An approach (not tested):selectc.firstname as cust_firstname,c.lastname as cust_lastname,v.firstname as vend_firstname,v.lastname as vend_lastnamefromcustomer cjoin Ziptable custzip on c.zipcode = custzip.zipcodejoin(select v1.firstname,v1.lastname,v1.zipcode,z.state from vendor v1 join Ziptable z on v1.Zipcode = z.Zipcode) as von v.state=custzip.state
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
Nested Query