October 26, 2010 at 7:35 am
I'm having an issue with a query that I'm writing, and I'm hoping someone can help me. I'll try to explain it as best as I can. I have three tables, a People table, a Positions table, and a People_Positions table.
I'm inserting records into the People table, then getting the IDs (there's an ID primary key) of those inserted records, which will eventually be inserted into the People_Positions table.
I'm also inserting the position records into the positions table and grabbing the ID from that table. I was hoping to combine the two ID fields and insert them into the People_Positions table.
I have no problems getting the IDs from the People table, because every person is unique. I have a problem with the Positions table because I'm joining on the PositionID and DepartmentID fields in order to get the ID, but that could exist many times, so I'm getting something like 10 IDs. When I go to insert into People_Positions, I'm inserting 10 rows for one person, as opposed to 1:
People_ID, PositionID
2,123
2,456
2,122
2,233
2,433
2,432
2,234
2,232
I'm not sure if that makes sense, but I'm just looking to grab the ID numbers from each of the tables so that I can insert one row per person in the People_Positions table, not the 10 that's resulting from the join to the Positions table.
Here's my query if that helps:
INSERT INTO People_Positions
(person_id,
position_id)
SELECT a.PersonID,
b.ID
FROM @EmpPosDept a
INNER JOIN Positions b
ON a.PositionID = b.PositionID
AND a.DepartmentID = b.DepartmentID
WHERE Dateadd(dd, 0, Datediff(dd, 0, b.AddDate)) = @currentDate
Thanks for any help.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
October 26, 2010 at 7:44 am
You don't quite have enough information. Is there not a key in the positions table? Is it allowed that there are multiple positions with a department? There's not enough sample data here for us to understand what's in the postiions table.
If so, perhaps you want to use a CTE and select DISTINCT from the positions table and then join to that distinct list.
October 26, 2010 at 7:49 am
Take a look at the Output clause for SQL 2005/2008. That should give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2010 at 8:58 am
Ok, I'll try to make it a little more clear. Let's say I have Jim Smith and John smith in the People table:
ID, EmployeeID, LastName, FirstName
1, M1234, Smith, Jim
2, M2222, Smith, John
If I have a list of Employee IDs, Positions, and Departments and want to get the ID of these employees, I could do:
SELECT b.ID
FROM MyListOfEmpIDPosDepts a
INNER JOIN People b
ON a.EmployeeID = b.EmployeeID
Correct? That would return one field (ID) with two rows for me, values 1 and 2.
Now, If John and Jim are both DBAs in the IT department, their records in the Positions table could look like this:
ID, Position, Department
66, DBA, IT
67, DBA, IT
I need to get the ID of 66 to link to one person's people record, and 67 to link to the other (it really doesn't matter which one is assigned to John or Jim because it's all the same position and department)
So ultimately, I need to build a People_Positions table that connects the two tables and looks like this:
ID, PeopleID, PositionID
88, 1, 66
89, 2, 67
Instead, what's happening is that I'm joining on Position and Department in the Positions table to get the IDs (I can't think of what else to join) and it's producing a result set that looks like this:
ID, PeopleID, PositionID
88, 1, 66
89, 1, 67
90, 2, 66
91, 2, 67
So where I have four records now, I should only really have two. I think the flaw is in my query against the Positions table:
...
INNER JOIN Positions b
ON a.PositionID = b.PositionID
AND a.DepartmentID = b.DepartmentID
Any ideas how I can get one ID from the Positions table for each person?
I hope that helps.
Thanks,
Mike
MyListOfEmpPosDepts
Mike Scalise, PMP
https://www.michaelscalise.com
October 26, 2010 at 9:08 am
mikes84 (10/26/2010)
...Now, If John and Jim are both DBAs in the IT department, their records in the Positions table could look like this:
ID, Position, Department
66, DBA, IT
67, DBA, IT
It seems that from this description someone has a poor idea of what the position department table is from a modeling perspective. The idea behind a separate table is that you have a single
66, DBA, IT
And both people link to it. Having separate items doesn't make sense here, unless there are other columns that somehow differentiate things here.
I need to get the ID of 66 to link to one person's people record, and 67 to link to the other (it really doesn't matter which one is assigned to John or Jim because it's all the same position and department)
So ultimately, I need to build a People_Positions table that connects the two tables and looks like this:
ID, PeopleID, PositionID
88, 1, 66
89, 2, 67
...
So where I have four records now, I should only really have two. I think the flaw is in my query against the Positions table:
The flaw is that without a way to differentiate things, this won't work. Ulltimately what you're looking at is a need to assign 66 to one person and 67 to the next, and while it could happen with very complicated SQL, likely it almost is easier with a cursor or RBAR to be sure you don't reassign something.
Really you should have a distinct ID from dept/position that is used to identify the person with a job. Is there a reason why you need a 66 for one person and a 67 for another? Would this not work?
Person
ID, EmployeeID, LastName, FirstName
1, M1234, Smith, Jim
2, M2222, Smith, John
Position
ID, Position, Department
66, DBA, IT
67, DBA, IT
Person_Position
PersonID, PositionID
1, 66
2, 66
October 26, 2010 at 9:14 am
mikes84 (10/26/2010)
...Now, If John and Jim are both DBAs in the IT department, their records in the Positions table could look like this:
ID, Position, Department
66, DBA, IT
67, DBA, IT
I need to get the ID of 66 to link to one person's people record, and 67 to link to the other (it really doesn't matter which one is assigned to John or Jim because it's all the same position and department)
This could be the root of your problem. Why have two rows for exactly the same entity? You should have one row representing Position = DBA and Department = IT, ID = 66. The John and Jim have the same position, 66. Not 66 and 67.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 26, 2010 at 10:15 am
I should have specified. Yes, there are more columns in the Positions table that differentiate the rows, such as Phone number, Preferred title, etc.
Also, I'd rather not use a cursor. Does that leave just complicated SQL? I hate to use that, but all I have to work with initially for each of these Position records is the PositionID and DepartmentID. Can anyone think of a function/functions I can use to get the one position record per person? I wonder if I could rank them somehow and go about it that way?
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
October 26, 2010 at 10:22 am
you could use row_number to differentiate them, but it would be complicated to somehow determine that 66 is assigned to one person and 67 to the next, 68 to the next, etc.
If these are separate items, then is there some other relation in the other fields that relates to the person? If so, it appears you still have incorrectly modeled the entity.
October 26, 2010 at 10:28 am
Unfortunately, there is no other way to determine their relationship.
Initially, this database was modeled differently (made a lot more sense and would not have caused this problem). However, it was requested by others that I model it differently to accommodate the administrative interface that users will be using to manage it (I know that's a horrible way to approach it). I've also had to replace all NULLs in the database with empty string. Not a huge fan of that either, but now I'm just trying to get the last piece in place to meet the specifications.
Mike Scalise, PMP
https://www.michaelscalise.com
October 26, 2010 at 11:24 am
Is the underlying model that a Position is a specific atomic work assignment with a 0..1 relation with employees?
IE, the organization might have 100 Positions, of which 85 are currently filled by 85 of 90 Employees, with the other 15 positions not assigned, and the other 5 employees in a training, leave of absence or other non-position state? (I could see the need for this if the organization has a federated funding or awkward budget situation -- university department, government agency, joint venture, etc.)
If the cardinality is 0..1 to 0..1, you don't need an association entity; map the assignment to one entity or the other.
October 26, 2010 at 11:37 am
Can a position have more than one person assigned to it? It sounds like they can't.
If not, add a PersonID to the Positions table, and get rid of the People_Positions table altogether.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2010 at 2:02 pm
I think I'm with GSquared. Sounds like the person/position link needs just a FK in one table or the other.
October 26, 2010 at 2:13 pm
Both of you are correct. It makes the most sense to add PersonID to the Positions table. However, based on how this administrative interface works, it looks like it NEEDS that association table in order to enforce the constraints (which makes absolutely no sense to me). I have a workaround in place now, where I place the PersonID in the positions table temporarily, so that I can join to it and get the id, then build the People_Positions table, then update the placeholder field in Positions back to empty string. It's disgusting, but it works.
Thanks for all of your guys' help.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
October 26, 2010 at 2:57 pm
Good on solving it. Make sure that higher ups in the business/organization know that this is NOT how it should work. You shouldn't be designing a bizare database to fit some admin tool.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply