MYSQL query with multiple wheres from same table

Question

So i have two tables jobs and contacts and Im trying to make a row that shows jobs that shows the job location and the tech assigned. Right now contacts can be techs or job locations I would like to keep it that way but can changed if needed. What is a good way to do this.

What is the right way to add the second where clause that would show location_label

SELECT 
  jobs.job_id, contacts.location_label, contacts.tech_label
FROM 
  jobs
LEFT JOIN 
  contacts ON jobs.tech_id = contacts.contact_id

jobs table

Field   Type    Null    Key     Default     Extra
job_id  int(6)  NO  PRI     NULL    auto_increment   
location_id     int(5)  NO      NULL         
tech_id     int(5)  YES         0        
creator_id  varchar(9)  NO      NULL     
creation    timestamp   NO      CURRENT_TIMESTAMP    
service_requested   varchar(400)    YES         NULL     
service_performed   varchar(400)    YES         NULL     
status_id   int(5)  NO      NULL

contacts table

Field   Type    Null    Key     Default     Extra
contact_id  int(9)  NO  PRI     NULL    auto_increment    
location_label  varchar(30)     YES         NULL     
address     varchar(20)     YES         NULL     
city    varchar(15)     YES         NULL     
state   varchar(2)  YES         NULL     
zip     int(10)     YES         NULL     
phone   int(11)     NO      NULL         
user_level  int(2)  YES         NULL     
first_name  varchar(10)     YES         NULL     
last_name   varchar(10)     YES         NULL             
type_tech   tinyint(1)  YES         NULL     
type_location   tinyint(1)  YES         NULL     
type_admin  tinyint(1)  YES         NULL     
tech_label  varchar(20)     YES         NULL     
creator_id  int(9)  YES         NULL

Im looking for row output to be jobs.job_id, contacts.location_label, contacts.tech_label

contacts.location_label would be the location of the job (jobs.location_id) contacts.tech_label would be the tech the job is assigned to (jobs.tech_id)

I found some other threads with similar issues but none of them seem to work properly thanks

Welcome to StackOverflow. It's pretty unclear to me what you're trying to accomplish... Can you post some sample data from each table and the output you're looking for from that data? — Ken White, Jul 13 '12 at 00:52
Show your sample table and the desired output so we can help — Prince Jea, Jul 13 '12 at 00:53

score 0 · Answer 1 · answered Jul 13 '12 at 02:55

You have to join the contacts table twice, one for tech and one for location.

SELECT J.job_id, 
       L.location_label, 
       T.tech_label
  FROM jobs J
  LEFT JOIN contacts T ON J.tech_id = T.contact_id
  LEFT JOIN contacts L ON J.location_id = L.contact_id

MYSQL query with multiple wheres from same table

1 Answers1