Join 3 tables?

[Jawa]

Dear All,

I hope someone can assist with this issue.

I have 3 tables Profile, Agent, Agency.

Profile
---------
profile_id
profile_name
company_id
company_name

Agent
-------
agent_id
agent_name


Agency
-------
agency_id
agency name


Basically profile.company_id can hold an ID from agent.agent_id or agency.agency_id OR the profile may not have profile.company_id at all and may only have a temporary company_name.


My goal is to create a query that will get all profiles and there corresponding agent_name or agency_name if there is an ID and get profiles for which there is no ID if there is a company name.

My queries thus far where I am doing simple joins are not giving the desired results. Any direction would be greatly appreciated.

THANK YOU!!!

 

[onpnt]

>> Any direction would be greatly appreciated.

First direction I think you should go down is normalization

I have to ask. Did you make company_id a foreign key to two other tables primary keys because you thought getting rid of a column was normalization?

The best advice I think I can offer is fixing the design far prior to writing queries on it. You are heading for a cave in with that setup. Trust me!

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}