Интересна Sql задача

[KoftiSector]

Здравейте,
онзи ден си блъсках главата над проблем, подобен на описаната задача, който така и не я реших само със средствата на Sql езика. Задачата ми се струва интересна, затава и реших да я споделя.
Нека са дадени следните таблици и тяхно примерно съдържание:
itemsidname1ааа2ббб, която описва обекти с номер и име;
sub_itemsiditem_idnameqty11si1ааа221si2ааа332si1ббб242si2bbb1, която описва подобекти и тяхнато количество в обект;
si_stor_areaidname1STRA_A2STRA_B, която описва места за съхраненение на подобекти;
sa_contentsi_stor_area_idsub_item_idqty1161241372312, която описва количеството подобекти по местата за съхранение

Да се състави заявка (или няколко), която(които) да показва(т), какви и колко пълни(комплектовани) обекта има по различните места за съхранение.

[dmanov]

SELECT items.name, si_store_area.name AS store, sa_content.qty DIV sub_items.qty AS quantity
FROM sa_content
 JOIN si_stor_area ON (sa_content.si_stor_area_id = si_stor_area.id)
 JOIN sub_items  ON (sa_content.sub_item_id = sub_items.id )
 JOIN items    ON (sub_items.item_id = items.id)

[KoftiSector]

SELECT items.name, si_store_area.name AS store, sa_content.qty DIV sub_items.qty AS quantity
FROM sa_content
 JOIN si_stor_area ON (sa_content.si_stor_area_id = si_stor_area.id)
 JOIN sub_items  ON (sa_content.sub_item_id = sub_items.id )
 JOIN items    ON (sub_items.item_id = items.id)

И върнатият резултат е:
aaa STRA_A 3
aaa STRA_A 1
bbb STRA_A 3
bbb STRA_B 6,
а правилният би следвало да е:
aaa STRA_A 1
и нищо повече

[Bibi]

Ако се направи с вложен селект: този да е вътрешния.
Външния да прави Left Join с резултатите от този и да търси MIN(quantity), групирано по артикулите в даден комплект (като счита NULL = 0).

Само че би възникнал проблем, ако е възможно в данните един и същи артикул да участва в повече от един комплекти, а по складовете да се брои за един. Т.е. при връзка много <-> много за обект:подобект.
Но в случая гледам, че не е така.
Искаш ли да го напиша като query?

[KoftiSector]

Искаш ли да го напиша като query?

Да това ще унагледи идеята ти.

[Bibi]

Например така:

  SELECT i.name, t.name AS store, MIN(t.quantity) AS pieces
   FROM items i
INNER JOIN  (SELECT sa.name, IFNULL(sc.qty DIV si.qty, 0) AS quantity, si.item_id
        FROM sub_items AS si
      LEFT JOIN si_stor_area AS sa ON true
      LEFT JOIN sa_content AS sc ON sc.sub_item_id = si.id AND sc.si_stor_area_id = sa.id) AS t ON i.id = t.item_id
 GROUP BY i.name, store
  HAVING pieces > 0

Така написано може да работи в MySQL. За други бази трябва да му се натъкми синтаксиса.

[KoftiSector]

SELECT i.name, t.name AS store, MIN(t.quantity) AS pieces
FROM items i INNER JOIN (SELECT sa.name, COALESCE(sc.qty / si.qty, 0) AS quantity, si.item_id
			FROM sub_items AS si
			LEFT JOIN si_stor_area AS sa ON true
			LEFT JOIN sa_content AS sc ON sc.sub_item_id = si.id AND sc.si_stor_area_id = sa.id 
			) AS t ON i.id = t.item_id
GROUP BY i.name, store
HAVING MIN(t.quantity) > 0

Така е за postgreSql и отговора е верен