Query help, I think I need a Cross join but not sure

  • I have the following tables.

    Restaurants

    ===========

    Rest Name,Group

    Taco Bell,1

    Taco Johns,1

    Burger King,2

    AppleBees,2

    Food

    ====

    Food Name,Group

    Tacos,1

    Nachos,1

    Pinacolda,1

    Hamburger,2

    Steak,2

    Apple pie,2

    Now I need the following results

    Results Table

    Rest Name,Food Name,Group

    Taco Bell,Tacos,1

    Taco Bell,Nachos,1

    Taco Bell,Pinacolda,1

    Taco Johns,Tacos,1

    Taco Johns,Nachos,1

    Taco Johns,Pinacolda,1

    Burger King,Hamburger,2

    Burger King,Steak,2

    Burger King,Apple Pie,2

    AppleBees,Hamburger,2

    AppleBees,Steak,2

    AppleBees,Apple Pie,2

    Thanks,

    Dean

  • Dean

    From your sample data, an inner join will be enough:

    SELECT RestName, FoodName, r.Group

    FROM Restaurants r INNER JOIN Food f

    ON r.Group = f.Group

    However, what happens if there is a food that isn't sold in any restaurant, or a restaurant that does not sell any of the foods?  Depending on the answer to that, it could be a LEFT, RIGHT or FULL OUTER JOIN that you need.  A CROSS JOIN would match every row in one table with every row in the other, giving you 24 rows in your result set from your sample data.  That isn't what you need here.

    John

  • Thank you John,

    That worked perfect!!

    Dean

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply