homepage Welcome to WebmasterWorld Guest from 54.227.77.237
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved