I have a car table that lists various properties and I have a property_group table that combines the various properties into different groups.
Now I want to know which cars fully fit in which property_groups.
With car names unique, this would be a simple join - but with repeating car names ???
car table
| id | name | property |
|---|---|---|
| 1 | ford | 1 |
| 2 | ford | 2 |
| 3 | nissan | 1 |
| 4 | nissan | 3 |
| 5 | nissan | 5 |
property_group tabe
| id | group | property |
|---|---|---|
| 1 | r01 | 1 |
| 2 | r0l | 2 |
| 3 | ks99 | 1 |
| 4 | ks99 | 3 |
| 5 | ks99 | 5 |
| 6 | uv55 | 1 |
| 7 | uv55 | 2 |
| 8 | uv55 | 3 |
| 9 | uv55 | 4 |
| 0 | uv55 | 5 |
expected result:
| name | group |
|---|---|
| ford | r01 |
| ford | uv55 |
| nissan | ks99 |
| nissan | uv55 |
CodePudding user response：
Hmmm . . . You can use join on the properties and then count how many properties match between the car the group:
select c.name, pg.group
from car c join
(select pg.*,
count(*) over (partition by group) as num_properties
from property_group pg
) pg
on pg.property = c.property
group by c.name, pg.group, pg.num_properties
having count(*) = pg.num_properties;
Note that group is a very poor name for a column, because it is a SQL keyword.
Here is a db<>fiddle.