Pomoc w skonstruowaniu zapytania Opcje

[_chris_]

Witam wszystkich
Dzisiaj stanąłem przed wyzwaniem, którego nie mogę ni jak pokonać. Do dalszej rozbudowy pewnej aplikacji muszę dodać jedną funkcjonalność. Ogólnie jest tak, że program teraz dobiera ilości produktów żywieniowych wg jakiś tam kryteriów. Tak czy inaczej wypluwa mi idProduktu i ilosc. Baza przedstawia się mniej więcej tak (ująłem tylko istotne właściwości) :

produkty:
idProduktu | nazwa

potrawy:
idPotrway | opis

skladnikiPotraw:
idPotrawy | idProduktu | ilosc

Program wypluwa na przykład: produkty - ilości (id - gram) : 1 - 20, 2 - 30, 3 - 40

Jak teraz mógłbym pobrać wszystkie potrawy spełniające wymagania, że wymagają do przygotowania tylko wybranych produktów, wybrana ilość jest wystarczająca do przygotowania potrawy. Mało tego, potrzebuję jeszcze wybrać takich potraw 21 (3 dziennie x 7 dni).

Z góry dziękuję za jakiekolwiek sugestie, bo stoję w martwym punkcie, a czas goni.

[bpskiba]

[SQL] pobierz, plaintext 
 SELECT pr.nazwa,sp.ilosc,pt.opis
 FROM produkty pr 
 JOIN skladnikiPotraw sp USING(id_produktu)
 JOIN potrawy pt USING(idPotrawy)
 WHERE
 ...............
[SQL] pobierz, plaintext 

[trafas]

Mi się wydaje że jest to bardziej złożony problem.

Z tego co zrozumiałem, to kolega chce podawać do zapytania produkty ( id produktów ) i w wyniku mają być zwrócone wszystkie potrawy, które zawierają tylko i wyłącznie podane produkty.

Zakładając takie dane wejściowe:

[SQL] pobierz, plaintext 
 
CREATE TABLE produkty
(
	idProduktu int,
	nazwa varchar(255)
);
 
CREATE TABLE potrawy
(
	idPotrawy int,
	opis varchar(255)
);
 
CREATE TABLE skladnikiPotraw
(
	idPotrawy int,
	idProduktu int,
	ilosc int
);
 
INSERT INTO produkty
SELECT 1, 'prod 1'
UNION
SELECT 2, 'prod 2'
UNION
SELECT 3, 'prod 3'
UNION
SELECT 4, 'prod 4'
UNION
SELECT 5, 'prod 5'
UNION
SELECT 6, 'prod 6'
 
 
INSERT INTO potrawy
SELECT 1, 'potr 1'
UNION
SELECT 2, 'potr 2'
UNION
SELECT 3, 'potr 3'
UNION
SELECT 4, 'potr 4'
UNION
SELECT 5, 'potr 5'
UNION
SELECT 6, 'potr 6'
 
 
 
INSERT INTO skladnikiPotraw
SELECT 1, 1, 12
UNION
SELECT 1, 2, 12
UNION
SELECT 1, 3, 12
UNION
SELECT 2, 1, 45
UNION
SELECT 2, 2, 10
UNION
SELECT 2, 3, 5
UNION
SELECT 2, 4, 18
UNION
SELECT 3, 5, 3
UNION
SELECT 3, 6, 7
UNION
SELECT 3, 1, 4
UNION
SELECT 6, 1, 5
UNION
SELECT 6, 2, 1
 
[SQL] pobierz, plaintext 

i taki warunek na wymagane produkty ( Jako że bawię się w konsoli, to zrobiłem sobie tabelę z id wymaganych produktów. Docelowo id mogą być przekazywane jako tablica w parametrze funkcji):

[SQL] pobierz, plaintext 
 
CREATE TABLE produkty_wymagane
(
	id_produktu int
)
 
INSERT INTO produkty_wymagane
SELECT 1
UNION
SELECT 2
 
[SQL] pobierz, plaintext 

to wynikiem powinna być tylko i wyłącznie potrawa ID 6


Wykombinowałem sobie takie zapytanie:

[SQL] pobierz, plaintext 
 
SELECT * 
FROM potrawy potr 
	JOIN skladnikiPotraw sklad ON sklad.idPotrawy=potr.idPotrawy
	JOIN (
			SELECT sp.idPotrawy, count(*) AS iloscProduktow
			FROM skladnikiPotraw sp
			WHERE 
				sp.idProduktu IN(SELECT * FROM produkty_wymagane)          -- ograniczenie na id produktów
				AND sp.idPotrawy NOT IN(
					SELECT idPotrawy 
					FROM skladnikiPotraw 
					WHERE 
						idPotrawy=sp.idPotrawy 
						AND idProduktu NOT IN(SELECT * FROM produkty_wymagane))  -- ograniczenie na ilość produktów w potrawach
			GROUP BY sp.idPotrawy
			HAVING count(*) >= (SELECT count(*) FROM produkty_wymagane)
		) AS skladProd
	ON skladProd.idPotrawy=potr.idPotrawy
	JOIN produkty prod ON prod.idProduktu=sklad.idProduktu
 
[SQL] pobierz, plaintext 

Czy o takie coś koledze chodziło ?


Jeżeli tak, to losowanie potraw 3 x 21 nie powinno już być problemem.

[_chris_]

Zapytanie jest genialne, robi robotę, ale jest jedno ale:) w tej tabeli produkty wymagane musi być drugie pole ilosc i ono też musi być brane pod uwagę. Dodatkowo zastanawiałem się nad wzięcie pod uwagę jakiejś odchyłki że jak potrawa wymaga 100g czegoś, a u mnie bedzie 98g no to tak czy inaczej możemy zrobić taką potrawę.

Dałbyś radę dołożyć jeszcze warunek ograniczający na ilość produktu w g ?

[trafas]

Dane wejściowe:

[SQL] pobierz, plaintext 
CREATE TABLE produkty
(
	idProduktu int,
	nazwa varchar(255)
);
 
CREATE TABLE potrawy
(
	idPotrawy int,
	opis varchar(255)
);
 
CREATE TABLE skladnikiPotraw
(
	idPotrawy int,
	idProduktu int,
	ilosc int
);
 
INSERT INTO produkty
SELECT 1, 'prod 1'
UNION
SELECT 2, 'prod 2'
UNION
SELECT 3, 'prod 3'
UNION
SELECT 4, 'prod 4'
UNION
SELECT 5, 'prod 5'
UNION
SELECT 6, 'prod 6'
 
 
INSERT INTO potrawy
SELECT 1, 'potr 1'
UNION
SELECT 2, 'potr 2'
UNION
SELECT 3, 'potr 3'
UNION
SELECT 4, 'potr 4'
UNION
SELECT 5, 'potr 5'
UNION
SELECT 6, 'potr 6'
 
 
 
INSERT INTO skladnikiPotraw
SELECT 1, 1, 12
UNION
SELECT 1, 2, 12
UNION
SELECT 1, 3, 12
UNION
SELECT 2, 1, 45
UNION
SELECT 2, 2, 10
UNION
SELECT 2, 3, 5
UNION
SELECT 2, 4, 18
UNION
SELECT 3, 5, 3
UNION
SELECT 3, 6, 7
UNION
SELECT 3, 1, 4
UNION
SELECT 5, 1, 100
UNION
SELECT 5, 2, 50
UNION
SELECT 6, 1, 5
UNION
SELECT 6, 2, 1
[SQL] pobierz, plaintext 

Zakres danych:

[SQL] pobierz, plaintext 
 
CREATE TABLE produkty_wymagane
(
	id_produktu int,
	wymagana_ilosc int
)
 
 
INSERT INTO produkty_wymagane
SELECT 1, 98
UNION
SELECT 2, 45
 
[SQL] pobierz, plaintext 

Wynik:

[SQL] pobierz, plaintext 
 
SELECT * 
FROM potrawy potr 
	JOIN skladnikiPotraw sklad ON sklad.idPotrawy=potr.idPotrawy
	JOIN (
			SELECT sp.idPotrawy, count(*) AS iloscProduktow
			FROM skladnikiPotraw sp
				JOIN produkty_wymagane pw ON pw.id_produktu=sp.idProduktu      -- produkty wymagane
			WHERE
				sp.ilosc BETWEEN pw.wymagana_ilosc - 10 AND pw.wymagana_ilosc + 10         -- wymagana ilość z zakresem +/- 10g
				AND sp.idPotrawy NOT IN(
					SELECT DISTINCT idPotrawy 
					FROM skladnikiPotraw 
					WHERE 
						idPotrawy=sp.idPotrawy 
						AND idProduktu NOT IN(SELECT id_produktu FROM produkty_wymagane))      -- ograniczenie na produkty spoza wymaganych idProduktu
			GROUP BY sp.idPotrawy
			HAVING count(*) = (SELECT count(*) FROM produkty_wymagane)        ------ warunek na ilość podanych produktów
		) AS skladProd
	ON skladProd.idPotrawy=potr.idPotrawy
	JOIN produkty prod ON prod.idProduktu=sklad.idProduktu
 
 
[SQL] pobierz, plaintext 

[_chris_]

Nieważne... Przydżumiła mnie coś ta dzisiejsza pogoda. Działa jak należy Dzięki wielkie!

Ten post edytował _chris_ 22.06.2012, 10:55:48