snehula - 1:41 pm on Jun 16, 2011 (gmt 0)

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 :-)