say i have the following table named foods.

- FOODS -
id | food
1 | taco
2 | burrito

and the following tables named taco and burrito

- TACO -
id | food_name
1 | soft taco
2 | hard taco

- BURRITO -
id | food_name
1 | fried burrito
2 | super good burrito

I want to be able to create a query that will select the id from foods and then use the food row to determine which database to use in a subquery.

select id, (select food_name from taco.my_food) from foods;

The taco part that is red in the query above is where i need to pull the reference from the foods table in the food row.

(please keep in mind this is a super generalized version of the query, so there are no where statements, etc)

Is this possible? (using mysql 4.1.12)

possible, but convoluted and clumsy

if there are no WHERE statements, you will end up joining row 1 of foods to taco table rows, and row 2 of foods to burrito table rows

so, the thing to do is LEFT OUTER JOIN to both tables -- and then for each value of foods id, only one of the other rows will match

how you are gonna fit this solution to your situation—which i'm betting is not about tacos and burritos—is your job

What you're looking at is a taxonomy of foods. Modelling each food type however using individual tables is not going to be the answer. What happens when a new food comes along? You insert a row into your foods table, however you now need to add a new table for the new particular food items.

To me it look rather similar to the manager -> employee situation in many businesses. And i know rudy has some good examples of that type of SQL modelling. Just get him to shed some (my brain isn't working properly yet today so I can't remember what that type of modelling is called).

it could be a reflexive relationship, an entity related to other instances of the same entity

or it could be a supertype/subtype relationship, with separate tables for each subtype

pretty hard to tell, from the meagre and probably fictitious example we've been given