January 24, 2005 at 9:39 pm
Hi, I have 2 tables, JobApplicants and OpenPositions.
JobApplicants looks something like this:
Name fkFirstChoice fkSecondChoice
Joe 1 2
Henry 2 4
OpenPositions looks something like
pkPositionID PositionTitle
1 Engineer
2 Programmer
3 ProjectManager
4 Accountant
How would I write a query that would give me output like this?
Name FirstChoice SecondChoice
Joe Engineer Programmer
Henry Programmer Accountant
Thanks!
Aaron
January 24, 2005 at 9:53 pm
Select
ja.Name,
op1.PositionTitle As FirstChoice,
op2.PositionTitle As SecondChoice
From JobApplicants As ja
Inner Join OpenPositions As op1
On ( ja.fkFirstChoice = op1.pkPositionID )
Inner Join OpenPositions As op2
On ( ja.fkSecondChoice = op2.pkPositionID )
(The above is based on the unstated requirement that everyone selects 2 choices)
Are you sure you should be using Hungarian notation (or some derivation thereof) in column names ?
January 24, 2005 at 10:55 pm
Here is another way probably a little faster, using a function
select Name, dbo.PosTitle(fkfirstChoice), dbo.PosTitle(fkSecondChoice)
from JobApplicants
create function dbo.PosTitle(@Choice int)
returns varchar(50)
AS
Begin
declare @Title varchar(50)
Select @Title = PositionTitle from OpenPosition where pkPositionID=@Choice
Return (@Title)
End
Cheers,
Ben
January 24, 2005 at 11:15 pm
Thanks Ben. That worked beautifully.
Aaron
January 25, 2005 at 7:59 am
Yes. it worked.
Then along comes your manager and says, "Hey, we need to add a flag to indicate if a position is management or not and we need to add a flag for full-time versus contract. We need to see if applicants are choosing management positions, or if they prefer full-time to contract. How long will it take you to modify the report for me ?"
Now you realise that you need new columns in your result set but ... uh-oh, you chose a technical solution that uses a function to return a single scalar value.
What do you do ? Code 2 new functions to return IsManagement and IsFullTimePosition ? Or realise that the correct technical solution is a simple relational join and not a function ?
January 25, 2005 at 9:01 am
Select
ja.Name,
op1.PositionTitle As FirstChoice,
op2.PositionTitle As SecondChoice
From JobApplicants As ja
Inner Join OpenPositions As op1
On ( ja.fkFirstChoice = op1.pkPositionID )
Inner Join OpenPositions As op2
On ( ja.fkSecondChoice = op2.pkPositionID )
(The above is based on the unstated requirement that everyone selects 2 choices)
PW,
As you stated, the select works fine if everyone makes two choices. However it fails if someone makes only one choice. Debating the reasons why that would or would not happen is not important - it's enough to say that if the person has no second choice then the second join fails and the record for that person is omitted from the result set.
As a minimum I think you need an outer join for the second choice. You should probably use an outer join for the first choice, too, just to cover all the bases.
Select
ja.Name,
op1.PositionTitle As FirstChoice,
op2.PositionTitle As SecondChoice
From JobApplicants As ja
Left Outer Join OpenPositions As op1
On ( ja.fkFirstChoice = op1.pkPositionID )
Left Outer Join OpenPositions As op2
On ( ja.fkSecondChoice = op2.pkPositionID )
January 25, 2005 at 9:19 am
Exactly. And neither of our solutions are technically correct because neither of us have seen the requirements. Maybe a Null 2nd choice represents an incomplete, or in-progress state and shouldn't be included in reports ?
You could further argue the what-ifs of future requirements of a 3rd choice, then a 4th choice and whether the design is appropriate. Maybe an intersect table with a preference ranking assigned to each Applicant/Position key pair ?
January 25, 2005 at 11:38 am
You guys are awesome. I love this debate. In reality, a second choice is not a requirement.
Aaron
January 26, 2005 at 6:31 am
Actually it's not a requirement yet...
That's how you should think about your db when you design it.
January 26, 2005 at 9:47 am
Really ?
So even though there's no stated requirement for a 3rd, 4th, 5th choice, you'd lock yourself into a design that uses repeating columns of the same foreign key, rather than normalizing out to an intersect table ?
I guess if you don't think that way when designing your DB you at least guarantee job security when the inevitable requirements changes force you to re-design the DB and write code for migration scripts for previously entered data.
January 26, 2005 at 9:54 am
Sorry I spoke too soon... what I meant to say is that I would make another table with the choices (maybe with a priority column) to allow for unlimited numbers and make a query with a single join to fetch the data.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply