[thelist] SQL: conditional ANDs and ORs. Totally stumped.

Francis Marion francis.marion at sfroy.com
Fri Mar 30 09:44:43 CDT 2012


Hey all, I've got a bit of an SQL toughie that I'm stuck on, and sure would appreciate some feedback.

I have to search a relationship table ' wp_term_taxonomy' containing your typical many-to-many data: term_id, parent, taxonomy (all INTs).

SELECT wp_term_taxonomy.term_id
    ,  wp_term_taxonomy.parent
    ,  wp_term_taxonomy.taxonomy
FROM wp_term_taxonomy
WHERE wp_term_taxonomy.taxonomy = 'sfr_places_categorie'
ORDER BY wp_term_taxonomy.parent;

+---------+--------+----------------------+
| term_id | parent | taxonomy             |
+---------+--------+----------------------+
| 9       | 0      | sfr_places_categorie |
| 10      | 0      | sfr_places_categorie |
| 11      | 0      | sfr_places_categorie |
| 15      | 9      | sfr_places_categorie |
| 14      | 9      | sfr_places_categorie |
| 13      | 9      | sfr_places_categorie |
| 12      | 9      | sfr_places_categorie |
| 16      | 10     | sfr_places_categorie |
| 17      | 10     | sfr_places_categorie |
| 20      | 10     | sfr_places_categorie |
| 18      | 11     | sfr_places_categorie |
| 19      | 11     | sfr_places_categorie |
| 21      | 11     | sfr_places_categorie |
+---------+--------+----------------------+

I will be searching against a comma delimited string of INTs (the results of a series of checkboxes in a search form) that will look like this: 16,18,15,20 from a form that basically resembles this:
FRUIT
   [X] Apple
   [X] Orange
   [  ] Pear
VEGGIE
   [X] Carrot
   [  ] Broccoli
   [  ] Celery
MEAT
   [X] Beef
   [  ] Chicken
   [X] Porc



I want to create a query on that table that does the following:

For each SUB category, the relationship should be an OR, but for each group of sub categories, the relationship should be an AND. In the above example, I would do a search for:

(Apple OR Orange) AND (Carrot) AND (Beef OR Chicken)



Suggestions on how to approach this?


--
Francis Marion
Département communications interactives
Signé François Roy

   819 336-6746 x 236
1 888-235-1175, poste 236
francis.marion at sfroy.com



More information about the thelist mailing list