I'm trying to pull ALL the info for a given "contact" into one MySQL result set. Here's my query:
SELECT contacts.*,
parent_contacts.account_id AS parent_account_id, parent_contacts.contact_prefix AS parent_contact_prefix, parent_contacts.contact_fname AS parent_contact_fname, parent_contacts.contact_mdl_init AS parent_contact_mdl_init, parent_contacts.contact_lname AS parent_contact_lname,
parent_accounts.account_name AS parent_account_name,
accounts.account_name,
time_zone_ops.tzone_abbr, time_zone_ops.tzone_value, time_zone_ops.tzone_location,
departments.dept_value
FROM contacts
LEFT JOIN contacts AS parent_contacts ON contacts.contact_parent_id=parent_contacts.contact_id
LEFT JOIN accounts AS parent_accounts ON parent_contacts.parent_account_id=parent_accounts.account_id
LEFT JOIN accounts ON contacts.account_id=accounts.account_id
LEFT JOIN time_zone_ops ON contacts.tzone_id=time_zone_ops.tzone_id
LEFT JOIN departments ON contacts.dept_id=departments.dept_id
WHERE contacts.contact_id=3
But when I run this, I get the following error:
Error: Unknown column 'parent_contacts.parent_account_id' in 'on clause'
If I take off the "parent_accounts.account_name AS parent_account_name,
accounts.account_name," and "LEFT JOIN accounts AS parent_accounts ON parent_contacts.parent_account_id=parent_accounts.account_id" clauses in the query, it works and returns a value of 0 for the parent_account_id for this query. So how can I adjust this query to account for that possibility. MOST of the results will not have a parent_contact_id either.
