Re: Conditional Join (or something along those lines)

Your SQL:

SELECT
    r.region_name, r.region_no,
    cj.relationship,
    c.name_last,
    c.name_first,
    c.email
FROM
    ((region r
LEFT JOIN
    contact_join cj
ON
    ((cj.parent_id = r.region_id)))
LEFT JOIN
    contact c
ON
    ((c.id = cj.contact_id)))
WHERE
    (cj.parent_table = 'region');



The WHERE clause will get rid of all the work of your LEFT JOINS, since
parent_table won't = 'region' for the non-present halves, it will be null. A
kludgy solution would be to change the "WHERE parent_table = 'region'" to
"WHERE parent_table = 'region' OR parent_table IS NULL"; a much better
solution would be to change the ON clause of the first LEFT JOIN to
"parent_id = region_id AND parent_table = 'region'"

I haven't tested this -- in the future, if possible, it's nice to post a
small dump of your problem set (in addition to your clear explanation),
allowing list members to recreate your tables by just cutting & pasting the
DDL statements to test our advice.

- J.