SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Mar 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    JOIN statement for two tables with multiple identifiers (MySQL)

    Hi,

    How do I make a SELECT statement to return data from two tables where the identifier in the “ON” statement is multiple? (MySQL database.)

    Table 1, columns orders.order_id, orders.order_customer_id,
    Table 2, columns, customer_id, first_name

    There are a lot more information in both tables but I stripped it off to make it easier to understand.

    The first table stores orders with unique order numbers (order_id), and the customer_id (order_customer_id) and the second table all customer information, each customer uniquely identified by customer_id.

    What I want to do is select all orders and tell who the customer is by name. I use the following which works fine:

    SELECT orders.order_id, orders.order_customer_id, customers.first_name
    FROM orders
    LEFT JOIN customers ON orders.order_customer_id = customers.customer_id

    However, when a customer has made two, or more, orders, the query returns NULL instead of name for the second and following entries for the customer.

    There is probably a simple solution that I don’t see, being too caught up with JOIN statements!

    Grateful for any help!
    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you did a great job of cutting down the table descriptions to just those columns that are pertinent to the problem

    could you also please do the same by showing a couple of sample rows of data which illustrate the NULL problem

    your query looks fine so i'm wondering what the problem could be
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the swift posting!

    This is embarrassing… I checked books, searched the internet, tried different queries to make sure I understood how JOIN worked before posting. According to my understanding it should deliver the result I expected. But as it didn’t I thought I was wrong.

    Turns out that the query is OK, I get the right result. The problem was a typo… Wrong customer number, and a customer number that does not exist (in a series of 900 there are only 2 or 3 blanks and the typo was one of them). Any other number and it would have been detected as out of range, or given a result with erroneous name.

    I found the problem when preparing to post the result, who knows how long time it would have taken to find it otherwise!

    Times like this one learn –always look at the simplest explanation first!

    Thanks for the help, sorry to have wasted your time!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no problem, glad you got it sorted out

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •