| subquery help needed
|
snehula
msg:4326878 | 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 :-)
|
LifeinAsia
msg:4326934 | 3:53 pm on Jun 16, 2011 (gmt 0) |
Doing something like this is much easier at the application (PHP, .NET, etc.) level than in a SQL query.
|
Demaestro
msg:4326943 | 4:24 pm on Jun 16, 2011 (gmt 0) |
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
msg:4327756 | 1:44 pm on Jun 18, 2011 (gmt 0) |
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
msg:4327808 | 4:54 pm on Jun 18, 2011 (gmt 0) |
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
msg:4328175 | 9:10 am on Jun 20, 2011 (gmt 0) |
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
|
|
|
