Complex select

[abthomas]

Hello,

I want to do a select which takes into account a dynamic number of 'and's.

Eg:
Table 'People' holds Names and a 'likes' textfield
Table 'Likes' holds a list of numbers and 'likes': '1 - Chess', '2 - Poker' and '3 - Bridge'.

So if person 'Bob' likes Chess & poker his likes-text field holds '12', if 'Jane' likes all three her likes-text field holds '123'.

I present the user with check boxes for the list of likes, but I'm not sure how to build the select. It could read 'Select Name from People left inner join on likes where number=1' if they just want to see people who like Chess or 'Select Name from People left inner join on likes where ( number=1 or number=2 or number=3)' if they select all tick boxes.

I've got asp.net to dynamically add the checkboxes (so I just add another 'like' (eg 'basketball) and it'll add a checkbox to the screen), and I've worked out how to read the data in the checkboxes.

Should I just have a base select string 'Select Name from People left inner join on likes' and then add the data in the loop eg (pseudo code):

string = 'Select Name from People left inner join on likes'

Loop around the repeater.
if they've ticked the current box
string = string + "where number=" + tickboxnumber
endif
endloop

Or is there a better way?

Many thanks.

[Bill Crawley]

You could create an object to store the selection and the criteria and add the object to a collection. when the user has finished you simply build the select from the details stored on each object in the collection

[abthomas]

The solution was to build up a string - it was quite easy too

[Alsvha]

Be careful to guard against injection attack then, if building a string like that.

Personally - I'd properly have created a filter table and then use that to join into, or create the association in the code layer.