Rafael Laurindo Rafael Laurindo - 2 years ago 86
SQL Question

Query returning multiple identical rows instead one

I have the tables:


table has the attributes:

  • name

  • barcode

table has the attributes:

  • juice_id

  • ingredient_id

And the
table has

  • name

  • optional (boolean)

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.

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?

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download