Hi all,

My tables are:

COUNTRY COLOR COUNTRY_COLOR ======== ======== ============== ID NAME ID NAME COUNTRY_ID COLOR_ID == ==== == ==== ========= ======= 1 Israel 1 BLUE 1 1 2 France 2 WHITE 1 2 2 1

They represent countries and their flags' colors.

I need to get all countries that their flag contains a given array of colors. For example: given the array (1) the countries to be retrieved are Israel and France, and given the array (1, 2) the country to be retrieved is Israel only.

In short, each value in the given array must exist in the relation table for the a country so it would be retrieved.

Suppose my colors array is (1, 2), this is my query (I do not work directrly with the relation table):

SELECT C.ID FROM COUNTRY C WHERE C.ID IN ( SELECT CC1.COUNTRY_ID FROM COUNTRY_COLOR CC1 WHERE 1 IN (SELECT CC2.COLOR_ID FROM COUNTRY_COLOR CC2 WHERE CC2.COUNTRY_ID = CC1.COUNTRY_ID) AND 2 IN (SELECT CC2.COLOR_ID FROM COUNTRY_COLOR CC2 WHERE CC2.COUNTRY_ID = CC1.COUNTRY_ID) AND CC1.COUNTRY_ID = C.ID )

First of all, I am not sure that I have created the most optimized query, I am sure that it is pretty cumbersome. Second, this is a specific example, I could have an array of ten colors (in theory, of course) And third, I would like to know if there is a way to achieve this query in LLBLGen

Thanks in advance

I belive the following query would be an easier representation of what your trying to do:

SELECT c.Id FROM COUNTRY_COLOR cc INNER JOIN COUNTRY c ON cc.Country_Id = c.Country_Id WHERE cc.COLOR_ID IN (1, 2)

The problem with this SQL is that the list of color_id's are fixed. You can pass an XML string into a stored procedure and use it as your list values, but that technique has limitations.

Luckily you have LLBLGen and the FieldCompareRangePredicate.

Examples are available in the LLBLGen user manual including this page:

Generated code - The predicate system, Adapter

Search on FieldCompareRangePredicate and you'll find your answer!

and to do it in the db (with a subquery: WHERE .. IN (select... from ..) , use a FieldCompareSetPredicate!

First of all, I'll rearrange my tables to get more convenience:

COUNTRY

ID NAME == ==== 1 Israel 2 France

COLOR

ID NAME == ==== 1 BLUE 2 WHITE

COUNTRY_COLOR

COUNTRY_ID COLOR_ID ========= ======= 1 (Israel......) 1 1 (Israel......) 2 2 (France....) 1

Now, Paul - your query is different from my query. Yours returns (1, 1, 2) - as for Israel (twice) and France, while mine returns (1) - as for Israel only. Given the colors array (1, 2) I want only Israel to be retrieved, since according to the relation table only Israel has all of the colors (1, 2) - and France doesn't (France has a partial group of the given array, so it should not be retrieved). Using the "IN" operator like Paul did means that at least one of the values in the array is found, not all of them, and I need all of them.

And I'm also not sure how to use FieldCompareSetPredicate in order to achieve my goal.

I couldn't find the query myself, so I googled: http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/77dbd918c3d6920e

please skip over the first paragraphs in his babbling and you'll end up with the query you want.