MySQL - fetch from many-to-many table

Question

MySQL

Help with the request, please ...

There is a table with the names of stores `mags`:
`mag_id`, `mag_name`

a table with goods that are sold there `tovs`:
`tov_id`, `tov_name`

and a linking table `mag_tov`:
`mt_mag_id` , `mt_tov_id`

Assume there are goods 'milk', 'sausage', 'meat', 'cheese', 'vodka', 'tea', 'coffee', 'salt'. Each store sells several different products at the same time.

I just can’t figure out something - how to choose stores that sell several of the goods at once, for example, 'milk', 'meat' and 'tea' at the same time.

Answer 1

SELECT mags.*, COUNT(*) AS c
FROM mag_tov, tovs, mags
WHERE mt_tov_id = tov_id AND mt_mag_id = mag_id
AND tov_name IN ("ПЕРЕЧИСЛИТЕ_ЧТО_ХОТИТЕ")
GROUP BY mag_id
HAVING c = КОЛЛИЧЕСТВО_ТОГО_ЧТО_ХОТИИТЕ

if you need 'milk', 'meat' and 'tea'
then just nested queries in the muscle is a very big evil.
SELECT mags.*, COUNT(*) AS c
FROM mag_tov, tovs, mags
WHERE mt_tov_id = tov_id AND mt_mag_id = mag_id
AND tov_name IN ('молоко', 'мясо' и 'чай')
GROUP BY mag_id
HAVING c = 3

Answer 2

select mt.mt_mag_id from mag_tov mt
where mt.mt_tov_id in (select tov_id from tovs where tov_name in ('milk', 'meat' and 'tea'))

Answer 3

In general, JOIN is usually used to join "many-to-many" tables, because specifically designed to optimally join tables
SELECT m.*, t.* FROM (mags m JOIN mag_tov mt ON m.mag_id=mt.mt_mag_id) JOIN tovs t ON mt.mt_tov_id=t.tov_id WHERE tov_name IN ('milk', ' meat', 'tea')
You can also use LEFT OUTER JOIN and/or RIGHT OUTER JOIN if, for example, you want to display products that are not yet listed in the mag_tov table