Darkmatter5 Posted June 13, 2008 Share Posted June 13, 2008 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? Link to comment https://forums.phpfreaks.com/topic/110090-multiple-joins-and-wheres-in-q-mysql-query/ Share on other sites More sharing options...
fenway Posted June 13, 2008 Share Posted June 13, 2008 What's wrong with that query? Link to comment https://forums.phpfreaks.com/topic/110090-multiple-joins-and-wheres-in-q-mysql-query/#findComment-565018 Share on other sites More sharing options...
Darkmatter5 Posted June 13, 2008 Author Share Posted June 13, 2008 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! Link to comment https://forums.phpfreaks.com/topic/110090-multiple-joins-and-wheres-in-q-mysql-query/#findComment-565076 Share on other sites More sharing options...
fenway Posted June 13, 2008 Share Posted June 13, 2008 You can use OR in an ON clause. Link to comment https://forums.phpfreaks.com/topic/110090-multiple-joins-and-wheres-in-q-mysql-query/#findComment-565102 Share on other sites More sharing options...
Darkmatter5 Posted June 13, 2008 Author Share Posted June 13, 2008 can you explain with an example? Link to comment https://forums.phpfreaks.com/topic/110090-multiple-joins-and-wheres-in-q-mysql-query/#findComment-565108 Share on other sites More sharing options...
fenway Posted June 14, 2008 Share Posted June 14, 2008 LEFT JOIN byrnjobdb.surveys ON byrnjobdb.jobs.survey_id_2=byrnjobdb.surveys.survey_idOR byrnjobdb.jobs.survey_id_3=byrnjobdb.surveys.survey_id Link to comment https://forums.phpfreaks.com/topic/110090-multiple-joins-and-wheres-in-q-mysql-query/#findComment-565692 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.