Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Apr 2006
    Location
    Northbrook, IL
    Posts
    394
    Thanks
    8
    Thanked 6 Times in 6 Posts

    Question conditional JOIN possible using CASE to determine JOIN table?

    i have a contacts table that has the state/province id as one of its address fields and bcntry_id as another field. id's like to make a query that pulls out the state or province name from the correct table based on bcntry_id.

    i know it can be done with a union like this:

    Code:
    SELECT
      s.abbr
    FROM
      contacts c
      INNER JOIN states s ON (c.bstate_id = s.id)
    WHERE
      c.bcntry_id = 226
    
    UNION
    
    SELECT
      p.abbr
    FROM
      contacts c
      INNER JOIN ca_provinces p ON (c.bstate_id = p.id)
    WHERE
      c.bcntry_id = 38
    but i'd like something like this:

    Code:
    SELECT
      stprovs.abbr
    FROM
      contacts c
      INNER JOIN
        CASE bcntry_id
          WHEN 226 THEN states
          WHEN 38 THEN ca_provinces
        END stprovs
      ON (c.bstate_id = strprovs.id)
    i also ran into another caveat - you cannot use user variables to refer to table names in FROM clauses....this seems like it may suffer from the same limitation since a JOIN is essentially a fancified FROM/WHERE.

    is this possible? or something similar.

    thanks,
    Leon
    Last edited by Leeoniya; 04-02-2009 at 12:07 AM.

  • #2
    Regular Coder
    Join Date
    Apr 2006
    Location
    Northbrook, IL
    Posts
    394
    Thanks
    8
    Thanked 6 Times in 6 Posts
    with some re-indexing i got this working pretty well:

    Code:
    SELECT
      c.id,
      CASE bcntry_id WHEN 226 THEN s.abbr WHEN 38 THEN p.abbr END bstate
    FROM
      contacts c
      LEFT JOIN states s ON (c.bstate_id = s.id AND c.bcntry_id = 226)
      LEFT JOIN ca_provinces p ON (c.bstate_id = p.id AND c.bcntry_id = 38)
    executes in 1.0 - 1.3 secs for 23,000 records. This is also paginated for a webapp, displaying <= 100 recs/pg...so realistically this is more than acceptable performance at 0.1 secs/req.

    I'm still interested in the conditional dynamic JOIN possibility though for other applications where a solution like this is impractical and hard to maintain.
    Last edited by Leeoniya; 04-02-2009 at 12:41 AM.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    I have to ask: What's wrong with your original UNION idea??

    Did you benchmark it? My hunch would be that it would be at least as fast and quite possibly faster than the LEFT JOIN is.

    You do have a mistake in your current version, assuming you want to only get results from USA and Canada. Since you are doing a LEFT JOIN to both the states and ca_provinces, any records in contacts that are *NOT* from either of those will still get the c.id value and will get NULL for bstate.

    Not a big deal if no other countries in the contacts table and/or you *do* want the c.id for other countries.

    But if you intended to limit the results to only USA and Canada, you'd want to add a WHERE clause:
    Code:
    SELECT
      c.id,
      CASE bcntry_id WHEN 226 THEN s.abbr WHEN 38 THEN p.abbr END bstate
    FROM
      contacts c
      LEFT JOIN states s ON (c.bstate_id = s.id AND c.bcntry_id = 226)
      LEFT JOIN ca_provinces p ON (c.bstate_id = p.id AND c.bcntry_id = 38)
    WHERE c.bcntry_id IN ( 38, 226 )

  • #4
    Regular Coder
    Join Date
    Apr 2006
    Location
    Northbrook, IL
    Posts
    394
    Thanks
    8
    Thanked 6 Times in 6 Posts
    the union query is a bit slower, not much though...and the shorter the better for me. yes, i do want all contacts from other countries as well. i just dont have ref tables for their subdivisions since they seldom occur.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Okay, for sure you went the right way, then!

    Getting the countries other than USA & Canada via the UNION would be a royal pain.

    I don't think you are going to do better than what you have.

    Well, not strictly true.

    One thing you *COULD* do would be to create a unified StatesAndProvinces table. Instead of having the two separate tables.

    Easy enough to do:
    Code:
    SELECT 226 AS bcntry_id, id, abbr
    INTO StatesAndProvinces
    FROM states
    
    INSERT INTO StatesAndProvinces (bcntry_id, id, abbr)
    SELECT 38, id, abbr
    FROM ca_provinces
    And now do all your operations against a single table. Such as
    Code:
    SELECT c.*, sap.abbr
    FROM contacts AS c
    LEFT JOIN StatesAndProvinces AS sap ON (c.bstate_id = sap.id AND c.bcntry_id = sap.bcntry_id)

    Since neither the list of states nor the list of provinces is likely to change real often, why not?

    Surely would gain a lot of efficiency at the minor cost of a pretty small additional table.


  •  

    Posting Permissions

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