多条件关联表查询：如何筛选仅包含指定jobTypeValueId的Job记录

Hey there! Let's sort out this query problem for you—your third attempt was close, but we need to fix a key issue with how it filters data.

Why Your Third Query Isn't Working #

Your third approach uses WHERE jt.jobTypeValueId IN (23, 64) to filter records first. This means for jobId=3, even though it has a jobTypeValueId=99 record, that entry gets excluded before grouping. So when you count distinct values, it only sees 64 and returns a count of 2—incorrectly including jobId=3 in your results.

Solution 1: Group with HAVING to Validate All Values #

We need to verify two critical things for each job:

1. It has exactly two distinct jobTypeValueIds (23 and 64)
2. It has no jobTypeValueIds outside of that pair

Here's the query that handles both checks:

SELECT j.jobId, j.jobName
FROM Jobs j
JOIN jobType jt ON j.jobId = jt.jobId
GROUP BY j.jobId, j.jobName
HAVING COUNT(DISTINCT jt.jobTypeValueId) = 2
   AND SUM(CASE WHEN jt.jobTypeValueId NOT IN (23, 64) THEN 1 ELSE 0 END) = 0;

• The COUNT(DISTINCT ...) ensures we have exactly two unique values
• The SUM(CASE...) checks that there are zero records with values outside 23 and 64

Solution 2: Use EXISTS/NOT EXISTS for Explicit Logic #

If you prefer more readable, straightforward logic, you can use three subqueries to directly map to your requirements:

• Confirm the job has a record with jobTypeValueId=23
• Confirm the job has a record with jobTypeValueId=64
• Confirm the job has no records with other values

SELECT j.*
FROM Jobs j
WHERE EXISTS (
    SELECT 1 FROM jobType jt 
    WHERE jt.jobId = j.jobId AND jt.jobTypeValueId = 23
)
AND EXISTS (
    SELECT 1 FROM jobType jt 
    WHERE jt.jobId = j.jobId AND jt.jobTypeValueId = 64
)
AND NOT EXISTS (
    SELECT 1 FROM jobType jt 
    WHERE jt.jobId = j.jobId AND jt.jobTypeValueId NOT IN (23, 64)
);

This approach makes your intent crystal clear at a glance, which is great for maintainability.

Either of these queries will correctly return only jobId=2, which matches your desired result.

内容的提问来源于stack exchange，提问作者amartin94