Php – thesql & php – query for record with all or fewer values, but not more

formsMySQLPHP

(SOLVED, SEE END)

I need hints on the PHP or MySQL code structure for the following:

Let's say you have X unique boxes, and Y types of fruit. Each box can contain one of each type of fruit. Some boxes might have only 2 types, some might have every type. All boxes have at least one type.

For the query, the user has a list of checkboxes, one for each kind of fruit. (I assume these all feed into the same query array?)

My problem starts here:

If a user checks

apples, oranges, and grapes

how do I construct a query to return the following:

Box01 (apples, oranges)
Box02 (grapes)
Box03 (apples, oranges, grapes)

but NOT:

Box04 (apples, oranges, grapes, pears, bananas)

How do I limit the returns to records that contain the all given values in $fruit[] or less, but NOT any OTHER values even if the requested values are present? Also, does this sort of query have a special name I can use for future reference?

This is a real tough one for me, and I appreciate your time.

  • A

(PS – you needn't worry about formatting the output or anything like that, that was just to illustrate the concept. I'm interested strictly in the query code.)

EDIT – two have asked, sorry for not going into specific dbase details. I've got 3 tables: one describing each box, one describing each fruit, and a many-to-many table with 2 columns – boxids & fruitids. I believe that's what you both assumed anyhow.

Thank you everyone!

MY SOLUTION

Anax's code below helped me along, here's what I ended up doing:

table "box" has columns "boxid" (primary key) & "box description"
table "fruit" has columns "fruitid" (primary key) & "fruit description"
table "box_has" has columns "boxid" & "fruitid"

SELECT boxid FROM box WHERE
    boxid NOT IN (
        SELECT boxid FROM box_has WHERE fruitid NOT IN ('F01' , 'F02'))

Looking at the code from back to front, the innermost SELECT finds all the boxes that have fruit OTHER than the ones we want, excluding troublesome boxes that have what we want AND others.

The outer select then uses the box table, in which each boxid only appears once, to get a clean return of individual boxes without repeats that contain any of the fruit selected.

Thank you for the help, everyone!

Best Answer

How about

SELECT * FROM box WHERE boxId NOT IN (
  SELECT * boxId FROM box WHERE fruit NOT IN ("apples", "oranges", "grapes"));
Related Topic