SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    May 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Conditional Select/Join Across Three Tables

    Hi, I've been a long time lurker on these forums and now i find myself asking for help. I currently have this query working but i am wondering if there is a better way to do this.

    I am trying to get a list of transactions and the users tied to them for stats reporting, here is a basic outline of the table structures as follows:

    transactions
    id, customer_id, customer_valid, amount, processed_date

    valid_customers
    id, first_name, last_name

    invalid_customers
    id, first_name, last_name

    in the transactions table, the customer_valid is a boolean value which indicates whether the customer record resides in valid_customers or invalid_customers and i have achieved the information i want via this method

    select transactions.id, transactions.amount, if(customer_valid=0,valid_customers.first_name,invalid_customers.first_name) as first_name,
    if(customer_valid=0,valid_customers.last_name,invalid_customers.last_name) as last_name
    from `transactions`
    left join valid_customers on transactions.customer_id = valid_customers.id
    left join invalid_customers on transactions.customer_id = invalid_customers.id

    While it works, i just have this feeling that there might be a better way to do this any input is appreciated.

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Well, this is different:
    Code:
    SELECT t.id, t.amount, c.first_name, c.last_name
    FROM transactions t
    LEFT JOIN valid_customers c
    ON c.id = t.customer_id
    WHERE t.customer_valid = true
    UNION
    SELECT t.id, t.amount, c.first_name, c.last_name
    FROM transactions t
    LEFT JOIN invalid_customers c
    ON c.id = t.customer_id
    WHERE t.customer_valid = false
    I'm not positive if this is more efficient or not, but I think it is. I know it's a bit easier to read at the very least.

    The reason I think it is more efficient is because when you do a join, it basically appends all of those columns to the first table, lining them up using the ON value. If you are only joining one table at a time, it should be more efficient.

    I'm not sure about the (in)efficiency of conditional statements in SQL queries, but I try to avoid them when possible just because they make the query more difficult to read.

    If your tables are big enough, you could always do a few time tests. Just run equivalent queries while timing each one. Though, if you have small tables, the difference will be so minor that you won't even be able to tell.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pbjam View Post
    While it works, i just have this feeling that there might be a better way to do this
    yes, there is

    instead of setting customer_valid, remove it completely from the table

    then your query becomes
    Code:
    SELECT transactions.id
         , transactions.amount
         , COALESCE(valid_customers.first_name,invalid_customers.first_name) as first_name
         , COALESCE(valid_customers.last_name,invalid_customers.last_name) as last_name
      FROM transactions
    LEFT OUTER
      JOIN valid_customers 
        ON valid_customers.id = transactions.customer_id
    LEFT OUTER
      JOIN invalid_customers 
        ON invalid_customers.id = transactions.customer_id
    simple, yes?

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

  4. #4
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Wouldn't it get duplicate entries if there was the same customer_id in both valid_customers and invalid_customers, without being able to tell which is which?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    excellent point

    it is for this reason that you would never assign the same key value to both a valid customer and an invalid customer

    this strikes right at the heart of the difference between an entity and an attribute, which are the objects analyzed in ER (entity-attribute) modelling

    a customer is an entity, and whether that customer is valid or invalid should be an attribute

    if you decide to store them in different tables, for whatever reason, you would want to use unique identifying key values for both of them

    that way, if you should ever decide to merge the two tables back into one, with an extra status column, you won't have any key collisions

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

  6. #6
    SitePoint Member
    Join Date
    May 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it is for this reason that you would never assign the same key value to both a valid customer and an invalid customer
    I have been playing with the previous solutions and neither produces the results I need for the output because, as you noticed, both the valid_customers and invalid_customers tables are using auto-incremented non-unique keys so COALESCE() and UNION don't quite hold up in this environment.

    Naturally, I wouldn't have designed the database like this in the first place, but this is an add-on to a preexisting piece of software that was written by an in-house guy. I am not really on the hook (or the payroll) to restructure the database and the associating code so altering the database structure is out of the question. Sorry for not mentioning that in the first post, perhaps my solution makes a bit more sense

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    how can you tell if a customer in the valid table is the same as a customer in the invalid table?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  8. #8
    SitePoint Member
    Join Date
    May 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    how can you tell if a customer in the valid table is the same as a customer in the invalid table?
    I believe there is a check made on certain criteria at the insertion point. The actual customer tables contain a fair bit more data (including email and cc info).

  9. #9
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    I'm surprised that the UNION one doesn't work. It shouldn't interfere with the duplicate ids in the customer tables, since it doesn't try to select or UNION along them.

    Rather odd that it didn't. What kind of output does it give, compared to your query?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the UNION one doesn't work because for a given id value, it might return two different customers
    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
  •