Different records from same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Different records from same table

Author

Topic

wilshaw01
Starting Member

4 Posts

Posted - 2014-08-07 : 04:04:13

Hi, I have 3 tables:-

Applicants
Assigned
Employers

Some of the applicants are assigned to an employer.

I trying to work out how to show applicants that are not assigned, and at the same time show applicants that are assigned to one particular employer, example...

SELECT applicantsName FROM Applicants
LEFT JOIN Assigned ON Applicants.applicantID = Assigned.applicantID
WHERE Assigned.employerID = @EmployerID
AND
Assigned.applicantID = NULL

Obviously the above doesn't work, just a quick example to show what I'm trying to do.

Any help would be appreciated.

Wilshaw

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-07 : 05:17:32

this will give the applicants who are not yet assigned

SELECT applicantsName FROM Applicants
where applicantID not in(select applicantID from Assigned)

Javeed Ahmed

wilshaw01
Starting Member

4 Posts

Posted - 2014-08-07 : 09:44:33

quote:
Originally posted by ahmeds08

this will give the applicants who are not yet assigned

SELECT applicantsName FROM Applicants
where applicantID not in(select applicantID from Assigned)

Javeed Ahmed

Hi Javeed, thanks. The problem is I'm trying to get the applicants that are not assigned, and also the applicants that are assigned to one particular employer.

For example, if a company called Smith Technologies logs in, I want them to see all the applicants that are not assigned, and the applicants that have been assigned to Smith Technologies at the same time. Any suggestions?

Wilshaw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources