CODEWITHSUNDEEP
sql-server
Socrats
Socrats

Reputation: 133

SQL query to find matching groups of records between two tables

Suppose I have two tables:


Table "Shares":

+---------+
| share 1 |
+---------+
| share 2 |
+---------+

And table "Valuations"

+-------------+---------+
| valuation 1 | Share 1 |
+-------------+---------+
| valuation 1 | Share 2 |
+-------------+---------+
| valuation 2 | Share 1 |
+-------------+---------+
| valuation 3 | Share 1 |
+-------------+---------+
| valuation 3 | Share 2 |
+-------------+---------+

I would like to know if there is an SQL Server (T-SQL) query that starts from the "Shares" table and returns only those valuations containing both of the "share" records ("Share 1" & "Share 2") - an inner join between the two tables would also return "valuation 2" in the result set and that is not something that I want.

Any help would be appreciated,

Thanks

Upvotes: 0

Views: 389

Answers (1)

forpas
forpas

Reputation: 164099

Assuming that the column name in shares and valuations is share, can group by valuation and set the condition in the having clause:

select valuation
from valuations
group by valuation
having count(distinct share) = (select count(*) from shares)

If you want all the rows from valuations:

select * from valuations
where valuation in (
  select valuation
  from valuations
  group by valuation
  having count(distinct share) = (select count(*) from shares)
)

See the demo.

Upvotes: 2

Related Questions