SQL Server JOIN of 2 tables where cross all records

lsmepo6l 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(115)

Given these 2 tables

Table name: MyValues
| Id | IdValue |
| ------------ | ------------ |
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 1 | 11 |

Table name: MyConditions

Id	IdValue
7	10
7	11

How could I write a query to get the Ids from the MyValues table to return the ones that match all the records on MyConditions table where MyValues.IdValue = MyConditions.IdValue

So the result would be 1 (since Id 1 from MyValues table matches all records in MyConditions table)

sql-server

来源：https://stackoverflow.com/questions/76412944/join-of-2-tables-where-cross-all-records

1条答案

按热度按时间

tjjdgumg1#

This reads like a relational division problem. We can join, and filter with having :

select v.id
from myvalues v
inner join myconditions c on c.idvalue = v.idvalue
group by v.id
having count(*) = (select count(*) from myconditions)

This assumes no duplicate (id, idvalue) in myvalue , and no duplicate idvalue in mycondition . Otherwise, we would typically use distinct on one or both side of having :

having count(distinct v.idvalue) = (select count(distinct idvalue) from myconditions)

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server JOIN of 2 tables where cross all records

1条答案

相关问题

热门标签

最新问答