Dustin Dustin - 4 months ago 7x
Java Question

Three tables, two foreign keys and one confused programmer

So, I have myself all confused with how to pull info from three tables. Let me set this up fairly clearly (I hope)...

I have three tables

, and
. Each food truck can have multiple menuItems (has a truckID column tied to the FoodTruck truckID) and each menuItem can have multiple ingredients (menuID column tied to the MenuItem menuID).

So, I am thoroughly confused on the mysql command to get the information I truly want (joins are still fairly confusing to me) because in the end I would LIKE to be able to show a user the Food Truck's menu. It would look like one big menu consisting of menuItems which include ingredients.

If there is a select query that would get all of that information, what would the output be comprised of? I mean, how would it be presented? An array of the menuItems and then an array of the ingredients? If I got the info I could figure out how to deal with it as I am using JDBC...

Forgot to add: in the Menu table there are the columns (primary key: menuID, foodName, price, foodType, specialComments)... I need all of those. In the Ingredients table, I have name and menuID but I really only need to display name.

I have most of the queries done that I need. It is just that I am trying to display the full menu now. I am using the DAO design and prepared statements to sanitize the input. In doing that, I was hoping to be able to use a join or somehow get all of that pertinent info without resorting to several queries to my database


Yes you can do it with one query, using LEFT JOIN.

  * // I used *, you should modify it if you want to better control the output
FROM FoodTruck ft
LEFT JOIN MenuItems mi
  ON mi.truckID = ft.truckID
LEFT JOIN Indgredient ind
  ON ind.menuID = mi.menuID

Left join will keep records from the table on the left even no rows can be found in the table on the right.

Take note that rows from FoodTruck are likely to duplicate because you have more than one menu items for each truck. The same will also happend to MenuItem.