Search 2 tables tied with fields where one field has more than one value.

[blitzen]

Can you help with a fairly complex query?
It's sorta like
WHERE family.Position = food.Position
but I need something more like
WHERE family.Position LIKE '%value.food.Position%' (I don't think that syntax exists for mySQL).

Here's an example. Notice I added fields to the generic example found everywhere on the Net.

family Table:
Field names: Position, Age, Misc
Row1: Dad, 41, ""
Row2: Mom, 45, "T1"
Row3: Daughter, 17, "T1, T2"
Row 4: "Dog, Cat", "none", "T3"

food Table:
Field names: ID, Meal, Position
Row1: T1, Steak, Dad
Row2: T2, Salad, Mom
Row3: T3, Spinach, ""
Row4: T4, Tacos, Dad
Row5: T5, Soup, Junior

The rows in "family" are tied to the rows in "food" with family.Misc and food.ID.
family.Misc can contain more than one food.ID value (comma delimited).

EXAMPLE
I want to search for "Dad" or "Junior" in the fields
family.Position, family.Age, food.Meal, food.Position

I want values from only the "family" table (SELECT family.*).
The following rows in "family" should be returned from the above example:
Row1, Row2, Row3

Row2 is returned because it's tied to row T1 in "food".
Row3 is returned because it's tied to both rows T1 and T2 in "food".
Row4 is not returned because "Dad" doesn't show up in "family" Row 4 or in "food" where ID=T3.

(Row5 table "food" is not tied to any row in "family" so it's ignored.)

Any help would be appreciated.
Thank you!

[Matt]

I'm not sure that what you want to do is possible (or if possible, practical). I believe you would need an additional food IDs table that correlates with the family table. You would probably want to add an ID column to your family table.

Then the sample row: Daughter, 17, "T1, T2" becomes:
3, Daughter, 17 (in family table)
3, T1 (in family_food_ids table)
3, T2 (in family_food_ids table)

You would also remove the "Position" column from your food table. Now, if you want to know what kind of food Daughter likes, your query is:
SELECT Meal FROM food, family, family_food_ids WHERE family.Position='Daughter' AND family_food_ids.family_id=family.ID AND food.ID=family_food_ids.food_id

This is how I would tackle this, but I'm by no means an SQL expert

-Matt

[blitzen]

Thank you for the quick reply.

What stops me is being able to link one row in table "family" to two rows in "food" as described.

I guess it won't be possible and I'll have to think of another way to do this without a total restructure of the db.

[oheso]

It's probably possible, but the SQL commands involved will be rather intricate. I think you'll be better off replacing the 'Misc' column in the 'family' table with a new table describing a many-to-many relationship between 'family' and 'food'. From what I can see from the example, that's really what you're trying to capture with the 'Misc' column anyway.