Welcome to WebmasterWorld Guest from 54.146.201.80

Forum Moderators: open

Message Too Old, No Replies

subquery help needed

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

Junior Member

joined:May 3, 2011
posts: 64
votes: 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 :-)
3:53 pm on June 16, 2011 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5551
votes: 24


Doing something like this is much easier at the application (PHP, .NET, etc.) level than in a SQL query.
4:24 pm on June 16, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 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
}
1:44 pm on June 18, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

joined:July 3, 2006
posts: 3123
votes: 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...
4:54 pm on June 18, 2011 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 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
9:10 am on June 20, 2011 (gmt 0)

Junior Member

joined:May 3, 2011
posts: 64
votes: 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
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members