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.
