...

View Full Version : conditional JOIN possible using CASE to determine JOIN table?



Leeoniya
04-02-2009, 12:04 AM
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:


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:


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

Leeoniya
04-02-2009, 12:36 AM
with some re-indexing i got this working pretty well:


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.

Old Pedant
04-02-2009, 01:26 AM
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:


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 )

Leeoniya
04-02-2009, 02:04 AM
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.

Old Pedant
04-02-2009, 05:24 AM
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:


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


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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum