please help, select with keys problem

[lazydba]

hi again,

I do have another problem, i do have a database with a peculiar structure. consider these:

table 1: calls

call_type call_ref client_name contact_name

Opportunity 1000 ABC Co. John Doe
Task 1000 DEF Co Jane


table 2: opportunity

opp_id description

1000 potential for 1000 bags order

table 3: task

task_id description

1000 deliver today before noon

Question: How do i make a select statement to show the following:

client_name contact_name call_type description

ABC Co John Doe opportunity potential for 1000 bags order
DEF Co Jane task deliver today before noon

Thanks in advance for all replies.

Cheers!

[ronverdonk]

What is the relation between these 3 tables (to join on common values)? Lookt to me there is none.

Ronald :cool:

[lazydba]

i agree... however, it actually has a primary key which has links to other tables which I didn't show because they're not related to what I wanted to get.

going back, if you will look at the three tables, the only other "link" is the "call_type" which should tell us on what table to look for to search for the call_ref. In the first row of table calls, it's call_type is "Opportunity", and on the corresponding table, the row with opp_id=1000 has the description: "potential for 1000 bags order"

as for the second row, the call_type is "Task", and correspondingly, the call_ref on that table 1000 gives the description: "deliver today before noon"

sounds peculiar, isn't it?

unfortunately, this application is an off-the-shelf package running on mysql. I agree that the referencing does not conform to rdbms standards, but I need a script on how to put that on a single report. the initial thing that came into my mind is creating a script from a script and executing it to get the desired results, but I don't think it's a good design.

thanks for the reply, hope you can help.