SQL Server - Returning Multiple Rows Based on 'IF' Criteria

Question

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

"a large table in SQL server that has multiple rows for each entity" - ...so the data is denormalized? — Dai
– Dai, Commented Feb 17, 2022 at 10:12
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
– Stu, Commented Feb 17, 2022 at 11:04
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
– GarethD, Commented Feb 17, 2022 at 11:28

Zaynul Abadin Tuhin · Accepted Answer · 2022-02-17 10:13:18Z

1

use aggregation

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

answered Feb 17, 2022 at 10:13

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server - Returning Multiple Rows Based on 'IF' Criteria

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related