Can't get desired result from MySQL query
I am trying to write a query in MySQL with output in PHP, but it is quite complicated.
I have a table with 3 columns (yes, I know the database layout is not the best for this, but I cannot change that, the client has other software using this db with this exact location)
Amount - Product - Package
2 - Apple - Fruitbasket1
1 - Pear - Fruitbasket1
5 - Grape - Fruitbasket1
2 - Apple - Fruitbasket2
1 - Pear - Fruitbasket2
9 - Banana - Fruitbasket2
So far I have come up with this:
SELECT package
FROM `data`
WHERE package IN
(
SELECT package
FROM `data`
WHERE product = 'apple' AND amount = '2'
)
AND package IN
(
SELECT package
FROM `data`
WHERE product = 'pear' AND amount ='1'
)
AND package IN
(
SELECT package
FROM `data`
WHERE product = 'grape' AND amount = '5'
)
LIMIT 0,1
The problem is that when the customer fills in "apple" and "2" in the form, he will return all baskets containing 2 apples. I need an expression that will only accept exact products and their amounts in order to return one basket that these ingredients belong to, or nothing at all.
So, although 1 pear and 2 apples are in Fruitbasket1, it should not result in Fruitbasket1 unless the other part, namely 5 grapes, is specified by the client.
source to share
this query will return all packages where the only product is 2 Apples
select package from `data`
group by package
having count(case when amount = 2 and product = 'Apple' then 1 end) = 1
and count(*) = 1
if you want 1 pear and 2 apples for example
select package from `data`
group by package
having count(
case
when (amount = 2 and product = 'Apple') or (amount = 1 and product = 'Pear')
then 1
end) = 2
and count(*) = 2
you can also write this as
select package from `data`
group by package
having count(case when amount = 2 and product = 'Apple' then 1 end) = 1
and count(case when amount = 1 and product = 'Pear' then 1 end) = 1
and count(*) = 2
source to share
I only have Oracle, so you might have to tweak the syntax a bit, but after your original style, I added a "not in" section to make sure the basket contains only the fruits you are looking for. Here are some boxes with some examples:
create table data (amount integer, product varchar(12), package varchar(16));
insert into data values (2, 'Apple', 'Fruitbasket1');
insert into data values (1, 'Pear', 'Fruitbasket1');
insert into data values (5, 'Grape', 'Fruitbasket1');
insert into data values (2, 'Apple', 'Fruitbasket2');
insert into data values (1, 'Pear', 'Fruitbasket2');
insert into data values (9, 'Banana', 'Fruitbasket2');
insert into data values (2, 'Apple', 'Fruitbasket3');
insert into data values (2, 'Apple', 'Fruitbasket4');
insert into data values (1, 'Pear', 'Fruitbasket4');
SELECT package
FROM data
WHERE package IN
(
SELECT package
from data
WHERE product = 'Apple' AND amount = '2'
)
and package not in
(
SELECT package
from data
WHERE product <> 'Apple'
)
group by package
;
SELECT package
FROM data
WHERE package IN
(
SELECT package
from data
WHERE product = 'Apple' AND amount = '2'
)
AND package IN
(
SELECT package
FROM data
WHERE product = 'Pear' AND amount ='1'
)
and package not in
(
SELECT package
from data
WHERE product <> 'Apple' and product <> 'Pear'
)
group by package
;
source to share