Как осуществить выборку по данным из другой таблицы?

Question

[Leatington]

Есть таблица продуктов (products), где есть AUTO_INCREMENT поле (id), а также поле с названием продукта (name) и идентификатором производителя (serial), как вы понимаете, от одного производителя может быть несколько продуктов.

Ещё есть таблица отметок (spent), куда можно занести некачественный продукт, записывается уникальный идентификатор продукта (id с первой таблицы - product) и время занесения (issued).

Сейчас при выборке продуктов некачественным помечается только занесённый продукт, а нужно, чтобы отмечались все продукты производителя, при том изменять базу данных, чтобы заносить в таблицу идентификатор производителя - не вариант.

Нужно сделать, чтобы при выборке абсолютно все продукты от этого производителя среди других были помечены.

Сейчас мой запрос таков:

SELECT distinct `products`.`id`, `products`.`name`, `products`.`serial`, `spent`.`issued` as `spent` from `products` left join `spent` on `products`.`id` = `spent`.`product`

Answer 1

[alexalexes]

Если хоть один из продуктов производителя будет занесен в некачественные, то этот запрос для любого продукта этого производителя будет выводить spent = 1.

SELECT distinct p1.`id`,
 p1.`name`, 
 p1.`serial`, 
 case
  when (select count(*)
  from `products` as p2
   join `spent`  as s2 on p2.`id` = s2.`product`
                                     and s2.`issued` = 1 -- какое значение в качестве некачественного продукта ?
 where p2.`serial` = p1.`serial`) > 0
  then 1
  else 0
   end as `spent` 
from `products` as p1

Comments

[Дмитрий]

SELECT distinct p1.`id`,
 p1.`name`, 
 p1.`serial`, 
 exists (select 1
  from `products` as p2
   join `spent`  as s2 on p2.`id` = s2.`product`
                                     and s2.`issued` = 1 -- какое значение в качестве некачественного продукта ?
 where p2.`serial` = p1.`serial`) as `spent` 
from `products` as p1

[alexalexes]

Дмитрий, да, это более подходящий вариант. Я забываю, что в MySQL есть булевый тип в качестве вывода в атрибуте.

[Slava Rozhnev]

Не понятно что такое сериал. Если это серийный номер изделия то он будет разным у разных товаров одного производителя. Если это название производителля то почему serial а не manufacturer или еще лучше manufacturer_id со ссылкой на таблицу производителей?

[Leatington]

Дмитрий, спасибо вам большое, данный вариант больше всего подошёл и замечательно работает.
alexalexes, issued это дата занесения, если товар качественный, он просто не заносится в эту таблицу, поэтому issued в проверке не нужен был)
Slava Rozhnev, serial это идентификатор производителя, а не изделия, грубо говоря порядковый номер, поэтому с этим всё в порядке. Благодарю Вас за помощь.

Answer 2

[Slava Rozhnev]

Можно импользовать следующий подход:
Снфчала выберем производителей бракоделов:

select distinct manufacturer
    from spent s
    join products p on p.id = s.product_id;

Потом присоединим таблиуц бракоделов к таблице продуктов используя LEFT JOIN:

select 
    products.*, 
    case when bad_manufacturers.manufacturer is null then 'Хороший производитель' else 'Бракодел' end manufacturer_rate
from products
left join (
    select distinct manufacturer
    from spent s
    join products p on p.id = s.product_id
) bad_manufacturers using (manufacturer)

https://sqlize.online/sql/mysql80/f55574001241f544...