Page 1 of 1

Help with complex query

Posted: Mon Jun 19, 2006 2:31 pm
by charlielha
Hello.

I have a site which has a table that stores people information, Ei: Name, age, zip code, sex, etc. This site has different forms which people can filled out. When a form is filled out, the perosn gets tagged on the table with an id for the form they filled out and a sub id for which domain they came from, (the site is hosted on different domains and this is needed to track different advertising methods).

My clients needs to be able to look at these people by tag, sub tag and refin the serach by sex, zip code, etc. They can have any combination of tags and subtag. For example we have form 1, 2, 3 and 4 and domains 1, 2, 3, 4. The client then can search al forms 1, 2, and 3, from domains 3 and 4

After several tries I came up with a query like this:

select from * table name where tag='form1' and sub_tag='domain3' and sex='sex' and zip_code='zip code' OR tag='form2' and sub_tag='domain3' and sex='sex' and zip_code='zip code' OR tag='form3' and sub_tag='domain3' and sex='sex' and zip_code='zip code' OR tag='form1' and sub_tag='domain4' and sex='sex' and zip_code='zip code' OR tag='form2' and sub_tag='domain4' and sex='sex' and zip_code='zip code' OR tag='form3' and sub_tag='domain4' and sex='sex' and zip_code='zip code'

This works fine most of the time but it's a little slow and I sometimes get an error, depending on how complex I make the query, remember there are still more options like date range, etc.

I am trying to look for a way to do this search better.

Any ideas?

Posted: Mon Jun 19, 2006 2:36 pm
by John Cartwright
Moved to Databases.

Posted: Mon Jun 19, 2006 2:39 pm
by TheMoose
Use the IN() syntax for MySQL:

Code: Select all

SELECT * FROM tablename WHERE tag IN ("form1", "form2", "form3") AND sub_tag IN ("domain3", "domain4") AND sex='sex' AND zip_code='zip code'
A warning, however. The more items you add to the IN part, the slower the query gets.

Posted: Tue Jun 20, 2006 1:08 pm
by charlielha
The In() worked perfectly

Thanks