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

Matt Warden mwarden at gmail.com
Fri Mar 30 10:22:31 CDT 2012

On Fri, Mar 30, 2012 at 10:44 AM, Francis Marion
<francis.marion at sfroy.com> wrote:
> 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?

If I understand your situation correctly, and I do think I am missing
an important detail (explained below), I think you want a series of
EXISTS clauses. Based on what you laid out, I don't think you can do a
series of ANDs and ORs directly, because there is no single row that
will satisfy all those conditions. You are looking for a number of
rows that satisfy your conditions, connected together by some
[relationship condition].

  exists (select .... where [relationship condition] and term_id in ($fruitcsv))
  and exists (select .... where [relationship condition] and term_id
in ($vegcsv))
  and exists (select .... where [relationship condition] and term_id
in ($meatcsv))

The part I am missing from your explanation is the [relationship
condition], because the table you showed is just a list of
subcategories. Without the relationship condition, all you are really
confirming is that the subcategories in $fruitcsv, $vegcsv, and
$meatcsv actually exist in the table. That is not very useful,
particularly because you probably built the list from that table in
the first place. So, the above comes with the caveat that the nature
of the relationship condition could actually change what the best way
to approach this is.

Matt Warden

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list