August 29, 2007 at 8:59 am
select
DISTINCT e.empID,empName,Email,status,jobid
from
Emp e
left outer join Office o on e.empID = o.empID
left outer join Staff s on e.empID = s.empID
Result Set:
735 Steve steve@gmail.com A 9
739 Mark mark@yahoo.com A 9
794 Maureen maureen@suddenlink.com A 41
Has another table EmpJob(Id,empid,jobid).
When I use the above query I get multiple empid for one job id(one-many) but i need the result in both ways(many-many) for which i need to use EmpJob table for that.
August 29, 2007 at 9:09 am
A bit more information here would be real helpful. Can you post the relevent table DDL for all tables involved as well as sample data for each table and an example of the desired results for that sample data?
August 29, 2007 at 9:18 am
Well - a many to many is implemented in SQL using 2 1-to-many relations from the mapping table.
If you want to base a query on the many to many relationship, then the mapping table needs to be part of the query, probably as the first table (from which you relate to all of the others).
Also - looks to me that you have a relational integrity issue between employee and jobs. If the above query runs, then that likely means that you have a jobID field in the emp table, which entails a 1-many relationship (where job is the 1 side and emp is the many side); you also just said that there's a many-to-many there. So - either one of those two relations isn't correct, or the jobID in the emp table conceptually means something different (like, perhaps, the job they are CURRENTLY assigned to) than what is in empjob table (perhaps the list of all of the jobs they have ever worked on).
If they don't mean the same thing - you might care to rename one of them to remind you that they are NOT the same.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 29, 2007 at 10:11 am
The data in the EmpJob wud be something like this
ID Empid JobId
1 735 9
2 735 10
3 735 11
4 735 12
5 739 9
6 794 41
7 797 41
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply