Hi

I hope somebody can help me or point me in the right direction.

I have the following query

String findString = "select prov.province, " +
" count(con) " +
" from Province prov left outer join prov.addresss adr " +
" left outer join adr.consumerBrand conb " +
" left outer join conb.consumer con " +
" group by prov.province" +
"";

Which returns the following data:

WC : 0
Western Cape : 1

What I need to do now is limit the count of Consumer con using brandName. So if I run the following query

String findString = "select prov.province, " +
" count(con) " +
" from Province prov left outer join prov.addresss adr " +
" left outer join adr.consumerBrand conb " +
" left outer join conb.consumer con " +
" where conb.brand.brand = ? " +
" group by prov.province" +
"";

I get the following data:

Western Cape : 1

What I need to do is still to receive all the Provinces, but obviously the count of the consumers would be less, as I am now filtering the data. How do I specify that the where part of my query only applies to the consumer con. I will also need to add constraints onto the consumer, gender and age!

I am not quite sure how to achieve this. Any ideas would be appreciated.

Thanks
Jeff Singer

Hi,



I'm not 100% sure what you are trying to achieve.

It seems that what you are trying to achieve is both restrict and project on the same criteria, almost like saying 'give me the provinces where [some criteria] and don't give me the provinces where [the same criteria]'.

In your case you have restricted the province WC as there are no matches in this province.

Remember that you still retrict tuples that have been outer joined. The join type does not affect how the data is restricted by the where clause.

Hope this helps.


Go for it