CODEWITHSUNDEEP
sqlt-sql
Burt
Burt

Reputation: 7758

Given a job id bring back a list jobs where job applications have been made to both jobs

I have a requirement to find jobs and order them by the number of people who have applied for both jobs.

Here is what the table looks like:

CREATE TABLE [dbo].[JobApplication](
[Id] [int] IDENTITY(1,1) NOT NULL,
[JobId] [int] NULL,
[CvId] [int] NULL,
[MemberId] [int] NULL,
[CreatedDate] [smalldatetime] NULL,
[Source] [nvarchar](50) NULL,
[AgreedTerms] [bit] NULL,
[Authorised] [bit] NULL,
[ApplicationStatusId] [int] NULL)

And here is an attempt at the select statement. 

SELECT     JobApplication.JobId, JobApplication_1.JobId AS AppliedFor, COUNT(JobApplication_1.JobId) AS AppliedForCount
FROM         JobApplication INNER JOIN
                  JobApplication AS JobApplication_1 ON JobApplication.CvId = JobApplication_1.CvId
WHERE JobApplication.JobId <> JobApplication_1.JobId                  
GROUP BY JobApplication.JobId, JobApplication_1.JobId
HAVING      (JobApplication.JobId = 4279)
ORDER BY AppliedForCount DESC

What the query needs to do is given a job id (4279) bring back a list jobs where job applications have been made to both jobs and rank the job list by number of applications shared between both jobs.

I didn't write the sample query so please and can't see where it is going wrong, can anyone help please?

Upvotes: 0

Views: 151

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44336

You can't display the other jobs that was applied for (well you can, but not as you described), but you can pick one of them with an aggregate function like this:

SELECT     
JobApplication.CvId, 
MIN(JobApplication_1.JobId) AS oneoftheAppliedFor, 
COUNT(*) AS AppliedForCount
FROM JobApplication 
INNER JOIN
    JobApplication AS JobApplication_1 
    ON JobApplication.CvId = JobApplication_1.CvId
    AND JobApplication.JobId = 4279
    AND JobApplication_1.JobId <> 4279
GROUP BY JobApplication.CvId
ORDER BY AppliedForCount DESC

Upvotes: 1

Related Questions