CODEWITHSUNDEEP
mysqlsqlhibernate
pete
pete

Reputation: 2046

Query with advanced condition in "count"

Lots of cities, each city has a bunch of houses (1 or more), and each house has the fixed set of attributes. Each attribute has: characteristic_id (could be color, shape, or garage type), and value_id (red, green blue, for COLORS table, and pyramid, rectangle, etc for SHAPES etc).

I want all the cities that contain at least one blue house and at least one pyramid house (could be the same house too)

I want to be able to use Hibernate to accomplish this

But joining all the tables together I am unable to come up with a criteria that can select this. For example, if I specify characteristic_id = 1 for color, and value_id=2 for blue, and characteristic_id=2 for shape, and value_id=3 for pyramid, it will obviously return 0 rows because there is no such row that has two different values for characteristic_id.

In reality I would need to select all the cities that have at least one attribute such that characteristic_id=1 and value_id=2 AND at least one attribute such that characteristic_id=2 and value_id=3 (don't have to be from same house). In other words, select * from cities where count(attributes with characteristic_id=1 and value_id=2 belonging to city c) > 0 AND count(attributes with characteristic_id=2 and value_id=3 belonging to city c) > 0

Something tells me maybe group_by, count, or having could solve this problem, but I have experimented with these to no avail.

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

You need to join all the tables together. Then, you can solve your problem with a a group by and having clause:

select c.cityid
from cities c join
     houses h
     on c.cityid = h.cityid join
     attributes a
     on h.attributeid = a.attributeid
group by c.cityid
having sum(case when characteristic_id = 1 and value_id = 2 then 1 else 0 end) > 0 and      
       sum(case when characteristic_id = 2 and value_id = 3 then 1 else 0 end) > 0;

Upvotes: 1

Related Questions