Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    1

    Unanswered: query with row being used to identify database in a subquery

    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)
    Last edited by stevek1977; 07-07-07 at 15:53.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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).

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •