英文:
Select rows with matching columns and all columns match a value for id
问题
我尝试了以下查询：
SELECT DISTINCT a.id
FROM TABLE_1 a
INNER JOIN TABLE_2 b ON a.id = b.id AND a.Gen_ID = b.Gen_ID
WHERE a.id NOT IN (SELECT id FROM TABLE_2 WHERE Status <> 'A');
这个查询应该能返回你期望的结果：
| ID |
| --- |
| 1 |
| 4 |
这个查询会选择在表1中具有匹配id和gen_id的记录，但只有当表2中的相应id的所有Status列都是'A'时才会选择这些记录。
英文:
I want to select the distinct id from table 1 that matches the id and gen_id of table 2 only if the Status column results are all 'A' for that id.
TABLE_1
| ID | Gen_ID |
|---|---|
| 1 | 21 |
| 1 | 24 |
| 2 | 23 |
| 2 | 25 |
| 3 | 28 |
| 3 | 29 |
| 3 | 31 |
| 4 | 32 |
| 4 | 33 |
TABLE_2
| ID | Gen_ID | Status
| --- | --- |
| 1 | 21 | A
| 1 | 24 | A
| 2 | 23 | A
| 2 | 25 | B
| 3 | 28 | A
| 3 | 29 | B
| 3 | 31 | C
| 4 | 32 | A
| 4 | 33 | A
Desired result:
| ID |
|---|
| 1 |
| 4 |
I tried the following:
SELECT DISTINCT a.id
FROM TABLE_1 a
inner join TABLE_2 b on a.id = b.id and a.Gen_ID = b.Gen_ID and b.status ='A'
group by TABLE_1.ID
having SUM(case when b.status = 'A' then 1 else 0 end) = count(*);
But that seems to return any record with 'A' as Status.
Actual result:
| ID |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
What am I doing wrong?
答案1
得分: 0
从连接中删除b.status = 'A'：
SELECT a.ID, count(1) as totalCount, SUM(case when b.status = 'A' then 1 else 0 end) A_count
FROM TABLE_1 a
inner join TABLE_2 b on a.ID = b.ID and a.Gen_ID = b.Gen_ID
group by a.ID
将返回：
ID TOTALCOUNT A_COUNT
1 2 2
2 2 1
3 3 1
4 2 2
然后，通过总数与status = 'A'计数进行比较，将得到期望的输出：
SELECT a.ID
FROM TABLE_1 a
inner join TABLE_2 b on a.ID = b.ID and a.Gen_ID = b.Gen_ID
group by a.ID
having SUM(case when b.status = 'A' then 1 else 0 end) = count(1)
返回：
ID
1
4
英文:
Just remove b.status ='A' from the join :
SELECT a.ID, count(1) as totalCount, SUM(case when b.status = 'A' then 1 else 0 end) A_count
FROM TABLE_1 a
inner join TABLE_2 b on a.ID = b.ID and a.Gen_ID = b.Gen_ID
group by a.ID
will return :
ID TOTALCOUNT A_COUNT
1 2 2
2 2 1
3 3 1
4 2 2
Then a comparison of total count with the count where status = 'A' will give us the expected output:
SELECT a.ID
FROM TABLE_1 a
inner join TABLE_2 b on a.ID = b.ID and a.Gen_ID = b.Gen_ID
group by a.ID
having SUM(case when b.status = 'A' then 1 else 0 end) = count(1)
Return :
ID
1
4
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论