Query returning multiple identical rows instead one

Question

I have the tables: juices, juice_ingredients and ingredients.

The juices table has the attributes:

name
barcode

The juice_ingredients table has the attributes:

juice_id
ingredient_id

And the ingredients 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.

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?

To make it more clear and simple: you want to see the rows of juices that are not referenced by juice_ingredients (you actually only want to see the ones with a given barcode and you expect to be just one of this kind. But am I right with my simplification? If so, post the complete definitions of juices and juice_ingredients.) — Artur Opalinski
– Artur Opalinski, Commented May 28, 2016 at 5:14

Bohemian · Accepted Answer · 2016-05-28 22:56:34Z

2

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'

edited May 28, 2016 at 22:56

answered May 28, 2016 at 5:27

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

rplaurindo Over a year ago

Bohemin, this was the returned error:

ERROR:  function min(boolean) does not exist LINE 7: HAVING MIN(ingredients.optional) = true                ^ HINT:  No function matches the given name and argument types. You might need to add explicit type casts. ********** Error **********  ERROR: function min(boolean) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 226

Bohemian Over a year ago

@RafaelLaurindo OK fixed (it would have worked in other DBs - postgres is so strict!)

rplaurindo Over a year ago

It doesn't raise an error, but returns no records. :(

Bohemian Over a year ago

@RafaelLaurindo oops. got the logic around the wrong way (see edited answer). If you want all juices that don't have optional ingrediaents, remove the WHERE clause.

rplaurindo Over a year ago

It works. Congratulations. Thank you too. But, why GROUP BY 1, 2?

|

sstan · Accepted Answer · 2016-05-28 05:17:49Z

1

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)

answered May 28, 2016 at 5:17

sstan

36.8k7 gold badges54 silver badges72 bronze badges

1 Comment

rplaurindo Over a year ago

I adapted you to Postgres, and it worked. ...on (i.id = ji.ingredient_id ...) where ji.juice_id = j.id)

Collectives™ on Stack Overflow

Query returning multiple identical rows instead one

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related