subquery help needed

[snehula]

Hi there :-)

Being a newbie to sql, I have got my head around simple queries. My problem right now is, let's say I have 2 tables, employee and jobs. Every job has 2-3 employees assigned to it (by employee id), an operator and 1-2 technicians. now when I'm trying to output some details about a job, including the names of the employees assigned to it, i'm trying the following:

SELECT jobs.details, jobs.job_date, jobs.job_op, jobs.tech1, jobs.tech2, jobs.job_type, employee.firstname, employee.surname
FROM jobs LEFT JOIN employee ON employee.idEmployee = jobs.job_op OR employee.idEmployee = jobs.tech1 OR employee.idEmployee = jobs.tech2
WHERE idCustomer = 1

which comes up with a record structure something like:

details|job_date|job_op|tech1|tech2|job_type|firstname|surname
blaa...|blaa....|..1...|..2..|..3..|..blaa..|Mickey...|Mouse
same...|same....|..1...|..2..|..3..|..same..|Donald...|Duck
same...|same....|..1...|..2..|..3..|..same..|Hercules.|TheOne
blaa...|blaa....|..6...|..3..|..4..|..blaa..|blaaa....|blaa

as u see, the first 3 rows are referring to the same job, returning three rows instead of one, 1 for each of the employees associated with it, which is how i wrote the query. What i would in fact like to do is something like this:

details|job_date|operator....|technician1|technician2|job_type
blaa...|........|Mickey Mouse|Donald Duck|Pluto......|...
2ndjob |........|Dagobert....|Daisy......|Speedy G...|...

i dunno if this is possible at all, not to mention how to go about getting there.. help pleeease :-)

[LifeinAsia]

Doing something like this is much easier at the application (PHP, .NET, etc.) level than in a SQL query.

[Demaestro]

The Pseudo code for doing this at the application level is:

job_data = select * from jobs where idCustomer = 1

foreach (job in job_data) {
employee_data = select * from employee where idEmployee = job['job_op'] OR idEmployee = job['tech1'] OR idEmployee = job['tech2']

return_data = job + employee_data
}

[penders]

However, if you could do it all in a single DB query (letting the database do the work) wouldn't it potentially be more efficient and possibly quicker? I'm thinking if this is a busy multi-user application...

[syber]

Something like this should give you what you want:

 
SELECT details, date, employee.firstname + ' '+ employee.surname as operator,  
 (SELECT employee2.firstname + ' '+ employee2.surname  
 FROM jobs INNER JOIN employee AS employee2 ON employee2.idEmployee = jobs.tech1) AS tech1, 
 (SELECT employee3.firstname + ' '+ employee3.surname  
 FROM jobs INNER JOIN employee AS employee3 ON employee3.idEmployee = jobs.tech2) AS tech2, 
job_type  
FROM jobs INNER JOIN employee ON employee.idEmployee = jobs.job_op 
WHERE idCustomer = 1 

[snehula]

thanks lads.. syber, this comes back with an error saying "subquery returns more than 1 result" or something like that
anyway, i did it at application level like you guys suggested, thanks :-) i was just thinking the same as penders and was wondering if it's possible to improve efficiency this way