Please help with SQL query
-
Hi,
I have two tables as defined below
Table 1: JOBS
job_id name
111 blah x 1
222 blah x 2
333 blah x 3
444 blah x 4
Table 2: ACTIVITIES
id job_id activity_type_id
1 111 1
2 111 2
3 222 1
4 222 3
5 333 1
6 444 3
The query needs to return those job numbers which has ONLY one associated activity and activity type ID = 1 or 3. For the test data above, the correct query would return 2 job IDs: 333 and 444, and the job 111 and 222 are excluded because they have more than one associated activities even though they both have activity type of 1 or 3.
Here's my incompleted query statement
SELECT job_id
FROM jobs
WHERE job_id NOT IN (SELECT job_id
FROM activities
WHERE (activity_type_id = 1 OR activity_type_id = 3)
AND .... <-- need to add last and condition to check that the job has ONLY one associated activity.
Could you please help with the last AND condition.
Thanks in advance for your help.
Tuan
-
SELECT j.job_id
FROM Jobs As j
INNER JOIN Activities As a
ON (j.job_id = a.job_id)
WHERE a.activity_type_id IN (1, 3)
GROUP BY j.job_id
HAVING Count(*) = 1
-
Hi PW,
THanks very much for your quick help. I will try your code soon.
Tuan
-
Should have tested first - that code still returns job 111.
Here's a corrected, tested solution:
SELECT j.job_id
FROM #Jobs As j
INNER JOIN #Activities As a
ON (j.job_id = a.job_id)
GROUP BY j.job_id
HAVING COUNT(*) = 1
AND SUM(CASE a.activity_type_id WHEN 1 THEN 1 WHEN 3 THEN 1 ELSE 0 END) = 1
-
or
SELECT j.job_id
FROM [Jobs] j
INNER JOIN [Activities] a
ON a.job_id = j.job_id
GROUP BY j.job_id
HAVING COUNT(*) = 1
AND SUM(activity_type_id % 2) = 1
p.s. providing activity_type_id is not > 3
Far away is close at hand in the images of elsewhere.
Anon. -
Hi,
Try This One !!!
select * from (
select job_id , count(*) activity_type_id from activities
group by job_id)a
where activity_type_id=1
Thanks
Amit Gupta
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply