SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do you do multiple joins to the same table?

    How do you do multiple joins to the same table?

    Given an Address table that has both Billing and Shipping Addresses (2 records) for the same customer.

    Trying the below but I get Join Expression not supported

    SELECT OrderDetail.*
    FROM ( ( ( OrderDetail

    INNER JOIN [Order] ON OrderDetail.OrderNumber = [Order].[Order Number] )

    INNER JOIN Customer ON Customer.ID = [Order].BIllingId)

    INNER JOIN Customer ON Customer.ID = [Order].ShippingId)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by QuickBooksDev View Post
    How do you do multiple joins to the same table?
    with table aliases
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star 
      FROM ( (  
           OrderDetail 
    INNER 
      JOIN [Order] 
        ON [Order].[Order Number] = OrderDetail.OrderNumber
           )
    INNER 
      JOIN Customer AS b_cust
        ON b_cust.ID = [Order].BIllingId
           ) 
    INNER 
      JOIN Customer AS s_cust
        ON s_cust.ID = [Order].ShippingId
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks that worked but it took a while to see where the parenthesis go.

    I kept the death star for the time being

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you added parentheses?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I added the parentheses as in your example and it worked. Thanks!!!
    I had some confusion about the number of parentheses (why 2 when there were 3 joins, etc).


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •