I had a requirement in which one record was associated with a list of checkboxes that can sometimes grow. To meet this I uses a 1:m relationship. It may be better using an example.

A research application can come in and it can be of one or many projecttypes. It can also utilize one or more procedures, and be conducted in one or more departments. To take the easy way out, I created enums for projecttypes, procedures and departments. If there was a requirement for additional, I would just add another enum in the enumtype. And the dB design was: Application(1) : ProjectType(n) where projecttype had 2 columns Application_ID_FK(int) and Project_Type(varchar). Same goes on for procedures and departments.

The end user will see a list of checkboxes and make a determination as to which projecttype his application was and which departments it would be conducted in and what kind of procedures it utilized.

Now I have a requirement to do an advanced search wherein a user may want to find out all projects which are of projecttype type1 and type2 and conducted in dept1 and dept2 which also utilizes procedures 1 and procedure 2.

How should I go about building a query using llblgen with the exisiting design?

Would it be easier if I put all the projecttypes, depts and procedures in one field each like so |projType1||projType2||projType3| in projecttypecolumn, |dept1||dept2| in Dept column and |proc1||proc2| is proceduresutilized column and then search using 'like'

e.g if i want to find applications where projectype is of type1 and type3, I say where projecttypecolumn like '%|type1|%' and projecttypecolumn like '%|type3|%'

Or is there a better way to do this with my existing 1:n design using groups and having, but stillable to dynamically build the predicateexpression?

i would avoid the 'like' statement if possible; this is a not an effecient method.

would something like this work:

select  distinct 
              a.[application fields]
from       application a
             inner join project_type p on a.application_id = p.application_id
             inner join department d on a.application_id = d.application_id
             inner join procedure_utilized u on a.application_id = u.application_id
where   (p.project_id = 1 and p.project_id = 2 ...)
 and        (d.department_id = 1 and d.department_id = 20 ...)
 and        (u.procedure_id = 110 and u.procedure_id = 120)

That sql definitely will not work. One row of department cannot have dept_id 1 and 20 at the same time.

The answer might be in using group by and having clause, but building it dynamically might be cumbersome.

Im sure developers have faced this situation before. For example, how will you find users who have both the useradmin rights and the moderator rights where both of them may be in the same column in a table.

this may work instead sudo sql:

select  a.[application fields]
from    application a
where   (select count(p.project_id) 
    from    project_type p 
    where   a.application_id = p.application_id
     and    (p.project_id = 1 or p.project_id = 2 ...)) = [number of project predicates - 1]
and (select count(d.department_id) 
    from    department d 
    where   a.application_id = d.application_id
     and    (d.department_id = 10 or d.department_id = 20 ...)) = [number of department predicates - 1]
and (select count(u.procedure_id) 
    from    procedure_utilized u 
    where   a.application_id = u.application_id
     and    (u.procedure_id = 110 or u.procedure_id = 120 ...)) = [number of procedure predicates - 1]

you would need to create a new IEntityField for each where clause that would be defined as a scalar query to count the number of rows returned from each query projects,_deparements,_procedures. The result of this scalar query would need to equal the number of predicated defined in the scalar query minus 1 to account for the link to the parent query (application table).

If the subquery doesn't equal the predicates - 1 then you know one of the parmaters is missing. the final query output would look somthing like this:

select  a.application_id,
    a.application_name
from    application a
where   (select count(p.project_id) 
    from    project_type p 
    where   a.application_id = p.application_id
     and    (p.project_id = 1 or p.project_id = 2)) = 2
and (select count(d.department_id) 
    from    department d 
    where   a.application_id = d.application_id
     and    (d.department_id = 10)) = 1
and (select count(u.procedure_id) 
    from    procedure_utilized u 
    where   a.application_id = u.application_id
     and    (u.procedure_id = 110 or u.procedure_id = 120 or u.procedure_id = 130)) = 3

Very interesting approach. I could try this. Do you have a code sample to build this query?

this could be it:

http://llblgen.com/TinyForum/Messages.aspx?ThreadID=6475&HighLight=1

here is another post I just found which seems similar to what you want to accomplish. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8973 check out psandler post (3rd from the top) it has a query that may do what your looking for only cleaner than my previous example.