multiple joins and wheres in q mysql query

[Darkmatter5]

On my site I have a form that has multiple dropdown lists that are created by queries of various tables in my database. I have a query I need to make that queries all the information selected in those dropdown lists and creates a page based on the result. Now that you kind of understand what I'm trying to do let me ask how to do it in a detailed fashion.

 

Example tables:

 

--Jobs--

jobs_id

employee_id

client_id

 

--Employees--

employee_id

first_name

last_name

 

--Clients--

client_id

first_name

last_name

company_name

 

Now how can I build a query statement to include the tables from the referenced tables of Employees and Clients. Here is the query I'm working with:

 

SELECT jobs.job_id, employees.first_name, employees.last_name, clients.first_name, clients.last_name, clients.company_name

FROM byrnjobdb.jobs

LEFT JOIN byrnjobdb.employees ON byrnjobdb.jobs.employee_id=byrnjobdb.employees.employee_id

LEFT JOIN byrnjobdb.clients ON byrnjobdb.jobs.client_id=byrnjobdb.clients.client_id

WHERE jobs.job_id =  'job selected from dropdown list' AND

employees.employee_id =  'employee_id from jobs table record selected from dropdown list' AND

clients.client_id = 'client_id from jobs table record selected from dropdown list'

 

My desired output is something like:

job_id: job_id selected from dropdown list

employee_first_name: first_name from employees table matching the employee_id stored in employee_id of the record selected from the dropdown list

employee_last_name: last_name from employees table matching the employee_id stored in employee_id of the record selected from the dropdown list

client_first_name: first_name from clients table matching the client_id stored in client_id of the record selected from the dropdown list

client_last_name: last_name from clients table matching the client_id stored in client_id of the record selected from the dropdown list

client_company_name: company_name from clients table matching the client_id stored in client_id of the record selected from the dropdown list

 

Any ideas?

[fenway]

What's wrong with that query?

[Darkmatter5]

Okay I figured most of it out, but need help with one thing.  Here's my currently working query.

SELECT jobs.job_number, clients.first_name, clients.last_name, clients.company_name, counties.county, jobs.job_desc, jobs.job_loc, surveys.survey, subdivisions.subdivision, jobs.section, jobs.lot_blk, jobs.fb_pg, jobs.fnd_date, jobs.acerage, types.`type`, jobs.memo_info, employees.first_name, employees.last_name, jobs.vol_pg, jobs.estimate, jobs.amount, jobs.assign_date, jobs.completion_date
FROM byrnjobdb.jobs
LEFT JOIN byrnjobdb.clients ON byrnjobdb.jobs.client_id=byrnjobdb.clients.client_id
LEFT JOIN byrnjobdb.counties ON byrnjobdb.jobs.county_id=byrnjobdb.counties.county_id
LEFT JOIN byrnjobdb.surveys ON byrnjobdb.jobs.survey_id_1=byrnjobdb.surveys.survey_id
LEFT JOIN byrnjobdb.subdivisions ON byrnjobdb.jobs.subdivision_id=byrnjobdb.subdivisions.subdivision_id
LEFT JOIN byrnjobdb.types ON byrnjobdb.jobs.type_id_1=byrnjobdb.types.type_id
LEFT JOIN byrnjobdb.employees ON byrnjobdb.jobs.employee_id=byrnjobdb.employees.employee_id
WHERE jobs.job_id='1'

 

How can I had more joins to include something like "LEFT JOIN byrnjobdb.surveys ON byrnjobdb.jobs.survey_id_2=byrnjobdb.surveys.survey_id" and also "LEFT JOIN byrnjobdb.surveys ON byrnjobdb.jobs.survey_id_3=byrnjobdb.surveys.survey_id"? Basically how can I also make the link between the other two fields in jobs that also reference the same type of data in surveys? I have three fields in jobs named "survey_id_1, survey_id_2 and survey_id_3, they all reference surveys.survey_id because one job might be in multiple surveys. So how can I make the query for survey_id_2 and survey_id_3?

 

Thanks!

[fenway]

You can use OR in an ON clause.

[Darkmatter5]

can you explain with an example?

[fenway]

LEFT JOIN byrnjobdb.surveys ON byrnjobdb.jobs.survey_id_2=byrnjobdb.surveys.survey_idOR byrnjobdb.jobs.survey_id_3=byrnjobdb.surveys.survey_id