mysql - Creating a view with multiple tables? -

i trying create view 5 tables having issues when trying join last 2 tables together.

here current mysql query:

create view s_view     select ac.id, a.id account_id, a.name, a.description, a.industry, a.phone_fax, a.phone_office, a.shipping_address_street, a.shipping_address_city, a.shipping_address_state, a.shipping_address_postalcode, a.shipping_address_country, c.id contact_id, c.first_name, c.last_name, c.title, c.department, c.phone_home, c.phone_mobile, c.phone_work, c.primary_address_street, c.primary_address_city, c.primary_address_state, c.primary_address_postalcode, c.primary_address_country, ea.email_address     accounts     inner join accounts_contacts ac on a.id = ac.account_id      inner join contacts c on c.id = ac.contact_id      inner join email_addr_bean_rel er on er.bean_id = a.id     inner join email_addresses ea on er.email_address_id = ea.id 

and tables set in following way:

accounts (id, first_name, last_name, etc.)

contacts (id, name, etc.)

account_contacts (id, account_id, contact_id)

email_addr_bean_rel (id, bean_id (same account_id / contact_id), email_address_id)

email_addresses (email_address_id, email_address)

the issue having email_addr_bean_rel table has bean_id can reference either account_id or contact_id. therefore when join tables returns accounts email address want return both accounts email address , contacts email address?

i have feeling issue way joining tables unsure how move forward?

thanks!

you can set 2 separate aliases email_addr_bean_rel table , reference appropriately (because of structure have add aliased version of email_address well):

create view s_view     select ac.id, a.id account_id, a.name, a.description, a.industry,          a.phone_fax, a.phone_office, a.shipping_address_street,          a.shipping_address_city, a.shipping_address_state,          a.shipping_address_postalcode, a.shipping_address_country,          c.id contact_id, c.first_name, c.last_name, c.title,          c.department, c.phone_home, c.phone_mobile, c.phone_work,          c.primary_address_street, c.primary_address_city,          c.primary_address_state, c.primary_address_postalcode,          c.primary_address_country,          ea.email_address,          ea_c.email_address email_address_c     accounts     inner join accounts_contacts ac on a.id = ac.contact_id      inner join contacts c on c.id = ac.account_id      left join email_addr_bean_rel er on er.bean_id = ac.id         , er.bean_module = 'accounts'     left join email_addr_bean_rel er_c on er.bean_id = c.id         , er_c.bean_module = 'contacts'     left join email_addresses ea on er.email_address_id = ea.id     left join email_addresses ea_c on er_c.email_address_id = ea_c.id 

---

edit

since not have row accounts or contacts (if understand correctly comments above - thanks, @bk435) set left join. i've added specifying of bean_module column (although i'm not sure code using that).