Hepl with Shortlist Tables
-
Hi,
A little help here!
Let says, I've the following tables:
ShortlistTable
=============
shortlistID | EmployeeID | ApplicantName
01 | 12 | James
02 | 15 | Peter
03 | 12 | Xander
04 | 12 | John
05 | 15 | Benjie
ApplicantTable
==============
Applicant Name | Specialization
James | Gardening
Peter | Pottery
Xander | Gardening
John | Gardening
Trevor | Gardening
Benjie | Pottery
Supposed I logged on as EmployerID# 12 and again search for Applicants to be shortlisted who has "Gardening" as their specialization, the record set should return only:
ApplicantName | Specialization
Trevor | Gardening
since I have already shorlisted other applicants who have the similar Specialization.
But if I logged on with a different EmployerID, let's say, EmployerID#15 and then search for the same specialization, Gardening, the search result should be like this:
ApplicantName | Specialization
James | Gardening
Xander | Gardening
John | Gardening
Trevor | Gardening
since there's still no entry on the ShortListTable under EmployeeID#15 with the "Gardening" shorlisted applicants.
Is there a query for this dilemma that I have OR should I use a Stored Procedure for this matter. By the way, I'm using the .cfm (coldfusion language) as my web programming platform and MS SQL as my database server.
-
SELECT a.Applicant Name, a.Specialization
FROM [ApplicantTable] a
LEFT OUTER JOIN [ShortlistTable] s
ON s.ApplicantName = a.ApplicantName
WHERE a.Specialization = @Specialization
AND ISNULL(s.EmployeeID,0) <> @EmployeeID
Far away is close at hand in the images of elsewhere.
Anon. -
Right on spot, if not the case where original poster actually has an employee with id 0.
Change code above to
SELECT a.ApplicantName,
a.Specialization
FROM @Applicant a
LEFT JOIN @Shortlist s ON s.ApplicantName = a.ApplicantName
WHERE a.Specialization = @Specialization
AND (s.EmployeeID <> @EmployeeID OR s.EmployeeID IS NULL)
N 56°04'39.16"
E 12°55'05.25" -
E&OE
Far away is close at hand in the images of elsewhere.
Anon. -
I'll bite
... what is "E&OE"?--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
"Errors and Omissions Excepted"
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply