I recently upgraded to MySQL 4.1.9 hoping it would solve some issues I am having but no dice...
I have 4 tables I access to develop user info:
USER , ADDRESS, CONTACT_EMAIL (multiple email addresses per user), CONTACT_NUMBER
Using the following sql statement to gather info:
SELECT user.*, mainaddress.*, mainemail.*, ispemail.*, phonenumber.*, faxnumber.*
from user
join address as mainaddress on
user.broker_pk = mainaddress.address_owner_pk
join contact_email as mainemail on
user.broker_pk = mainemail.email_owner_pk
join contact_email as ispemail on
user.broker_pk = ispemail.email_owner_pk
join contact_number as phonenumber on
user.broker_pk = phonenumber.number_owner_pk
join contact_number as faxnumber on
user.broker_pk = faxnumber.number_owner_pk
WHERE
user.status = 1
AND mainaddress.address_type = 10
AND mainemail.email_type = 10
AND ispemail.email_type = 11
AND phonenumber.number_type = 10
AND faxnumber.number_type = 11
ORDER BY last_name asc
Problem being that when I use this statement in Navicat it works fine...when I test it in PHPMyAdmin I cannot use the ispemail.email_address as it does not recognize the table referral of ispemail. or mainemail. so I wind up with two email_address columns.
the pages that I am working with cannot see the table reference either. Not sure what to do.
Thank for any assistance you can provide.
