Can't Figure out this JOIN - 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)

Can't Figure out this JOIN

Author

Topic

Bortiquai
Starting Member

4 Posts

Posted - 2007-10-26 : 18:11:31

I have 2 tables I need to pull data from. Basically, it is customer information and order information.
In the customer Information (CustomerInfo), I've got the columns:
CustomerID, FirstName, LastName

In the Order Information (OrderInfo), I've got the columns:
OrderID, BillToCustomerID, ShipToCustomerID

As you can see, the billing and shipping information can be different. I need a SQL Statement that will display both, so the resultant dataset would look something like:

OrderID, ShipFirst, ShipLast, BillFirst, BillLast

I need the First and Last name from CustomerInfo where ShipToCustomerID = CustomerID
and in the same dataset, I need the First and Last Name from CustomerInfo where BillToCustomerID = CustomerID.

I've tried my luck at a few statements, but can't seem to get it.
Thanks

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-10-26 : 18:27:09

Something like this?

SELECT o.OrderID, b.FirstName BillToFirst, b.LastName BillToLast, s.FirstName shipToFirst, s.LastName shipToLast
FROM OrderInfo o
JOIN CustomerInfo b on b.CustomerID = o.BillToID
JOIN CustomerInfo s on s.CustomerID = o.ShipToID

---------------------------------------------------------
SSRS Kills Kittens.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources