-1
mysql> SELECT mi.foodName, mi.price, mi.calories, mi.specialComments, 
ind.ingredient FROM FoodTruck ft LEFT JOIN 
Menu mi ON mi.truckID = ft.truckID LEFT JOIN Ingredients ind ON 
ind.menuID = mi.menuID;
+----------------------+-------+----------+-----------------+-----------------+
| foodName             | price | calories | specialComments | ingredient      |
+----------------------+-------+----------+-----------------+-----------------+
| NULL                 |  NULL |     NULL | NULL            | NULL            |
| NULL                 |  NULL |     NULL | NULL            | NULL            |
| dustins dark rum     |   3.5 |      285 | yummy           | dark rum        |
| dustins dark rum     |   3.5 |      285 | yummy           | ice             |
| rad rachels rumchata |     3 |      375 | dairy free      | rumchata        |
| rad rachels rumchata |     3 |      375 | dairy free      | almond milk     |
| rad rachels rumchata |     3 |      375 | dairy free      | chocolate syrup |
| rad rachels rumchata |     3 |      375 | dairy free      | ice             |
| NULL                 |  NULL |     NULL | NULL            | NULL            |
| NULL                 |  NULL |     NULL | NULL            | NULL            |
+----------------------+-------+----------+-----------------+-----------------+
10 rows in set (0.01 sec)

So, here is mySql query and the result, which via a PreparedStatement, is held in a ResultSet. How, exactly do I get the info out? I know I can have: while(rs.next()) { int x = resultSet.getInt(); String y = resultSet.getString();}

but is there a way to NOT repeat the foodName, price, calories and specialComments when dealing with the resultSet? I just need that for dustins dark rum and rad rachels rumchata and then every ingredient. Basically, as you can see from my query, this is joined from two different tables based on info from a third. So... it is kind of complicated. Not sure how to proceed or if my sql query can be fine tuned... Thank you!!

drawnitsud
  • 55
  • 11
  • 1
    What do you mean not repeat? Do you want a solution that won't call `resultSet.getString("foodName")` on every iteration? – Mateusz May 30 '16 at 21:13
  • I just meant, when I called `resultSet.getString("foodName")` does resultSet make me do that twice because dustins dark rum is listed twice? Or is there a way for me to just pull each of those things only once but get every ingredient... Does that make sense? – drawnitsud May 31 '16 at 02:42

1 Answers1

0

You can not do it in 1 query. You can either execute 2 queries (one for the Menu and the second for the Ingredients.).

Your other option is to use something like the GROUP_CONCAT function in conjunction with a GROUP BY clause. See Can I concatenate multiple MySQL rows into one field?

Community
  • 1
  • 1
Gerhard
  • 146
  • 6