Select Query question - 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 2008 Forums

Transact-SQL (2008)

Select Query question

Author

Topic

zanq
Starting Member

3 Posts

Posted - 2012-09-20 : 16:30:16

How can I select the Customer information in the Customers table so I can get both:

CUSTOMERS data when CUSTOMERS.CUSTOMER_NO = SALES_ORDERS.CUSTOMER_NO

CUSTOMERS data when CUSTOMERS.CUSTOMER_NO = SALES_ORDERS.BILL_TO_CUSTOMER_NO

TABLES:

CUSTOMERS
CUSTOMER_NO
CUSTOMER_NAME

SALES_ORDERS
SALES_ORDER_NO
CUSTOMER_NO
SHIP_TO_ATTENTION
BILL_TO_CUSTOMER_NO

SELECT
a.CUSTOMER_NAME as 'Customer Name' --This is the Customer info
,a.CUSTOMER_NO as 'Customer #' --This is the Customer info
,b.SALES_ORDER_NO as 'Sales Order #'
,b.SHIP_TO_ATTENTION as 'Ship to Attention'
,a.CUSTOMER_NO --I want Bill To Customer Info (where a.CUSTOMER_NO = b.BILL_TO_COSTUMER_NO)
,a.CUSTOMER_NAME --I want Bill To Customer Info (where a.CUSTOMER_NO = b.BILL_TO_COSTUMER_NO)

FROM CUSTOMERS a
JOIN SALES_ORDERS b on b.CUSTOMER_NO = a.CUSTOMER_NO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 16:48:30

You can join on the customer table one more time like shown below:

SELECT a.CUSTOMER_NAME      AS 'Customer Name',
       a.CUSTOMER_NO        AS 'Customer #',
       b.SALES_ORDER_NO     AS 'Sales Order #',
       b.SHIP_TO_ATTENTION  AS 'Ship to Attention',
       c.CUSTOMER_NO AS BILLTO_CustomerNo,
       c.CUSTOMER_NAME AS BILLTO_CustomerName
FROM   CUSTOMERS a
       JOIN SALES_ORDERS b
            ON  b.CUSTOMER_NO = a.CUSTOMER_NO
       INNER JOIN CUSTOMERS c
            ON  c.CUSTOMER_NO = b.BILL_TO_CUSTOMER_NO

If ther is a possibilty that BILL_TO_CUSTOMER_NO in the SALES_ORDERS table is null, then use LEFT JOIN instead of INNER JOIN.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-20 : 16:49:34

Assuming there are no nulls you can do something like:

SELECT 
    *
FROM
    Sales_Orders
INNER JOIN
    Customers
    ON Sales_Orders.Customer_No = Customers.Customer_No
INNER JOIN
    Customers AS Bill_To_Customer
    ON Sales_Orders.Bill_To_Customer_No = Bill_To_Customer.Customer_No

EDIT.. ahh too slow! :)

zanq
Starting Member

3 Posts

Posted - 2012-09-20 : 19:25:08

Thanks guys. I didn't know that I could create another join to the same tables but it makes sense and that's what I'll do. Thanks for the quick response. This forum rocks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 07:16:29

You are welcome.

You can join the a table to itself (as many times as you wish, although if you go overboard with it, the performance will suffer). The only thing is that, you MUST alias the table names so SQL can detect which instance of the join you are referring to when you specify column names.

http://msdn.microsoft.com/en-us/library/ms177490(v=sql.105).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources