0

with a better explanation:

I'm working in SQL server joining tables that that have multiple rows for each entity - or 'House' in my example data. I want only those 'House UIDs' that have a type of Garden, Drive and Entrance hall in the table I'm joining to. It doesn't matter if they have more than that, but there must be House_UID rows with at least Garden, Drive and Entrance hall as a 'Thing type' value for the House_UID to be returned.

In the example data this would be 1 and 3.

I've tried

Select HOUSE_UID, THING_TYPE from HOUSES

Join HOUSE_THINGS on HOUSES.HOUSE_THING = HOUSE_THINGS.THING

WHERE HOUSE_UID in (select HOUSE_UID from houses 
                    Join HOUSE_THINGS on HOUSES.HOUSE_THING = HOUSE_THINGS.THING
                   where house_thing = x)
   and HOUSE_UID in (select HOUSE_UID from houses 
                    Join HOUSE_THINGS on HOUSES.HOUSE_THING = HOUSE_THINGS.THING
                   where house_thing = y)

   and HOUSE_UID in (select HOUSE_UID from houses 
                    Join HOUSE_THINGS on HOUSES.HOUSE_THING = HOUSE_THINGS.THING
                   where house_thing = z)

but this doesn't seem to work - I'm getting results that don't have rows for everything I'm interested in. Any new ideas welcome!

Thanks

Example Data

Improve this question
6
  • "a large table in SQL server that has multiple rows for each entity" - ...so the data is denormalized?
    – Dai
    Commented Feb 17, 2022 at 10:12
  • GROUP BY, HAVING etc
    – jarlh
    Commented Feb 17, 2022 at 10:13
  • Please avoid posting images of data, sample data should be consumable text in your question, ideally as create and insert statements, or alternatively a DB<>Fiddle. See the question guide.
    – Stu
    Commented Feb 17, 2022 at 11:04
  • @Dai no the data is normalized
    – Steve
    Commented Feb 17, 2022 at 11:12
  • This is known as Relational Division, and there are a few different techniques available depending on your exact requirements. See also High Performance Relational Division in SQL Server
    – GarethD
    Commented Feb 17, 2022 at 11:28

1 Answer 1

Reset to default
1

use aggregation

select houseid from table_name
where house in ('Garden', 'Drive','Entrance hall')
group by houseid
having count(distinct house)>=3
Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.