vendredi 21 août 2020

MYSQL join random row from another table for each row

I have two tables: Foods and Ingredients (refer to Image 1 below).

Image 1 enter image description here

I wish to randomize the Ingredients to each Foods (can be duplicate ingredients for all Foods). How to use query to retrieve the price too? Thanks

I have tried using below SQL but not the result I wanted (refer to Image 2) because if using sample SQL 1 the ingredients guaranteed same for all rows. If using sample SQL 2 the price is also randomized not match with the respective ingredients.

/* sample SQL 1 */
select a.description, b.description, b.price
from Foods a
join (select a1.* from Ingredients a1 order by rand() limit 1) b
;

/* sample SQL 2 */
select a.description, (select a1.description from Ingredients a1 order by rand() limit 1) as description, (select a1.price from Ingredients a1 order by rand() limit 1) as price
from Foods a

Image 2 enter image description here




Aucun commentaire:

Enregistrer un commentaire