[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