CODEWITHSUNDEEP
sqlpostgresql
rplaurindo
rplaurindo

Reputation: 1404

Query returning multiple identical rows instead one

I have the tables: juices, juice_ingredients and ingredients.

The juices table has the attributes:

The juice_ingredients table has the attributes:

And the ingredients table has

For the sake of customer's logistics various juices may have the same barcode, but with different ingredients, some of which are optional I need select, by barcode, the single juice that has no contains optional ingredient among its ingredients.

I signed up four ingredients: water (optional: false), sugar (optional: true), pineapple pulp (optional: false) and mint (optional: true). And signed up four juices: one only with water and pineapple pulp, other with water, pineapple pulp and sugar, other with water, pineapple pulp and mint, and other with water, pineapple pulp, mint and sugar. All with the same barcode. I make a query to select only the juice make with non optional ingredients, in this case water and pineapple pulp.

SELECT *
FROM juices
INNER JOIN juice_ingredients ON (juice_ingredients.juice_id = juices.id)
INNER JOIN ingredients ON (juice_ingredients.ingredient_id = ingredients.id)
WHERE juices.barcode = '000000000001' AND ingredients.optional = false

But it was returning multiple rows. What should change this query to bring only one, or the juice containing no optional ingredients in the composition?

Upvotes: 0

Views: 68

Answers (2)

Bohemian
Bohemian

Reputation: 424953

You could do it with a having clause:

SELECT juices.*
FROM juices
JOIN juice_ingredients ON juice_ingredients.juice_id = juices.id
JOIN ingredients ON juice_ingredients.ingredient_id = ingredients.id
WHERE juices.barcode = '000000000001'
GROUP BY 1, 2
HAVING MAX(ingredients.optional::text) = 'false'

Upvotes: 2

sstan
sstan

Reputation: 36473

Since you didn't specify which database you are using, you may have to adjust the SQL for your specific database:

select *
  from juices j
 where j.barcode = '000000000001'
   and not exists (select *
                     from juice_ingredients ji
                    inner join ingredients i
                       on (i.ingredient_id = ji.ingredient_id
                      and i.optional = true)
                    where ji.juice_id = j.juice_id)

Upvotes: 1

Related Questions