'AND' 'OR' not working together

Databases
1

I’m having some trouble with a database query and am not sure what I’m doing wrong. What I’m trying to do is get the results where some fields meet the requirements (i.e. WHERE x = 1 and y = 2) which works fine, but when I try to include an ‘OR’ cause the results don’t match.

This is what I’m using but I’m not sure what I’m doing wrong.

SELECT * from ((feeds INNER JOIN flavour ON feeds.product_feed_id = flavour.product_id) INNER JOIN allergens ON feeds.product_feed_id = allergens.product_id) WHERE brand_name IN ("Youngs" OR "Birds Eye" OR "Own brand") and weight_id IN ("0" OR "3" OR "5") and price >=25 and price <=570 and enabled=1 and stock=1 and deleted=0 and allergens_id IN ("1" OR "6") and allergens_id IN ("5" OR "8");

2

That equals IN(TRUE), cf. https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_in

1 Like
3

Sorry to ask a dumb question but how do I then change it so that it gets the results where either of those values is present?

4

Have you read the linked page?

5

I did and tried to replace the ‘or’ with ‘,’ instead but that then returned no results at all. I reduced the query as much as possible and should definitely have gotten results

6

Since we neither know your database nor your data nor what your current attempt is, there is not much we can do.

although I have to admit the whole statement looks weird.

7

I have written queries similar to

SELECT sometable.field 
FROM sometable 
WHERE sometable.id IN 
  (SELECT othertable.id 
  FROM othertable ....

and queries similar to

SELECT sometable.field 
FROM sometable 
WHERE sometable.id IN 
  (1, 2, 3, 4) .....

but I have only used OR for comparisons not inside what is expected to be an array.

The query looks like it has other problems, but fixing that would be a place to start.

8

here’s your problem, assuming you changed the ORs to commas –

and allergens_id IN ( 1 , 6 ) and allergens_id IN ( 5 , 8 );

think about this for a while, you’ll figure it out soon :slight_smile:

9

Thank you all for your help but I’m afraid I still can’t get it working. If I keep it as allergens_id IN ( "5 " or "8" ) then it gets the wrong results, but if I change it to allergens_id IN ( 5 , 8 ), allergens_id IN ( '5 , 8' ) or allergens_id IN ( '5' , '8' ) it returns nothing at all.

I really don’t know who to fix this and am happy paying someone to help get this working.

:frowning:

10

please show your latest query

11

Thank you, the latest is

SELECT product_name, weight, weight_id, serving, price, brand_name, protein_100, fat_100, calories_100, carbs_100, aw_deep_link, product_feed_id, image_url, best_seller from (((((((feeds INNER JOIN timing ON feeds.product_feed_id = timing.product_id) INNER JOIN flavour ON feeds.product_feed_id = flavour.product_id) INNER JOIN sweeteners ON feeds.product_feed_id = sweeteners.product_id) INNER JOIN source ON feeds.product_feed_id = source.product_id) INNER JOIN dietary ON feeds.product_feed_id = dietary.product_id) INNER JOIN ingredients ON feeds.product_feed_id = ingredients.product_id) INNER JOIN product_categories_map ON feeds.product_feed_id = product_categories_map.product_id) WHERE brand_name IN ("Youngs", "Birds Eye" , "Own brand") and weight_id IN ("2", "0") and ingredients.ingredients_id IN ("12", "3", "9") and price >=0 and price <=250 and enabled=1 and stock=1 and deleted=0 GROUP BY product_name ORDER BY best_seller DESC LIMIT 20

I’ve got one main table that contains most of the details I need, but then I’ve also got other tables that have various other details like ingredients etc

12

thanks, i can’t read that, so i reformatted it –

SELECT product_name
     , weight
     , weight_id
     , serving
     , price
     , brand_name
     , protein_100
     , fat_100
     , calories_100
     , carbs_100
     , aw_deep_link
     , product_feed_id
     , image_url
     , best_seller 
  from (((((((
       feeds 
INNER 
  JOIN timing 
    ON feeds.product_feed_id = timing.product_id
       ) 
INNER 
  JOIN flavour 
    ON feeds.product_feed_id = flavour.product_id
       ) 
INNER 
  JOIN sweeteners 
    ON feeds.product_feed_id = sweeteners.product_id
       ) 
INNER 
  JOIN source 
    ON feeds.product_feed_id = source.product_id
       ) 
INNER 
  JOIN dietary 
    ON feeds.product_feed_id = dietary.product_id
       ) 
INNER 
  JOIN ingredients 
    ON feeds.product_feed_id = ingredients.product_id
       ) 
INNER 
  JOIN product_categories_map 
    ON feeds.product_feed_id = product_categories_map.product_id
       ) 
 WHERE brand_name IN ("Youngs"
                    , "Birds Eye" 
                    , "Own brand") 
   and weight_id IN ("2"
                   , "0") 
   and ingredients.ingredients_id IN ("12"
                                    , "3"
                                    , "9") 
   and price >=0 
   and price <=250 
   and enabled=1 
   and stock=1 
   and deleted=0 
GROUP 
    BY product_name 
ORDER 
    BY best_seller DESC LIMIT 20

my observations –

  1. parentheses in the FROM clause make it look like MS Access, but incomplete GROUP BY and use of LIMIT tell me it’s MySQL – maybe remove the parentheses, okay?

  2. use of doublequotes as integer delimiters seems strange and could get them mistaken as identifiers – maybe remove them, okay?

  3. failure to qualify all columns with their table names means it’s really hard to debug (e,g, weight_id versus ingredients.ingredients_id – maybe fix this, okay please?

  4. the problem with allergens_id mentioned in post #9 is missing

if you could please fix these issues and re-post, thanks

13

Thank you so much for helping with this, when you say remove ‘parentheses’ in the from clause do you mean remove from altogether? I’ve decided the remove the allergens_id from post #9 altogether as it wasn’t needed, bu thank you for pointing it out as I hadn’t noticed the mistake originally.

I’ve added the table names it too:

SELECT product_name
	, weight
	, weight_id
	, serving
	, price
	, brand_name
	, protein_source
	, protein_100
	, fat_100
	, calories_100
	, carbs_100
	, aw_deep_link
	, product_feed_id
	, image_url
	, best_seller
	from (((((((
	feeds 
INNER JOIN timing ON feeds.product_feed_id = timing.product_id) 
INNER JOIN flavour ON feeds.product_feed_id = flavour.product_id) 
INNER JOIN sweeteners ON feeds.product_feed_id = sweeteners.product_id) 
INNER JOIN source ON feeds.product_feed_id = source.product_id) 
INNER JOIN dietary ON feeds.product_feed_id = dietary.product_id) 
INNER JOIN ingredients ON feeds.product_feed_id = ingredients.product_id) 
INNER JOIN product_categories_map ON feeds.product_feed_id = product_categories_map.product_id) 
WHERE brand_name IN ('Youngs', 'Birds Eye', 'Own brand') 
and feeds.weight_id IN ('2', '0')
and ingredients.ingredients_id IN ('12', '3', '9')
and feeds.price >=0 and feeds.price <=250 and feeds.enabled=1 and feeds.stock=1 and feeds.deleted=0 GROUP BY feeds.product_name ORDER BY feeds.best_seller DESC LIMIT 20

The INNER JOIN tables have a field that’s the table name and _id (so timing_id for example) and I want to get the product_id (for example timing.product_id) of thoses where it matches the timing_id and then show the corisponding results from the feeds table where timing.product_id = product_feed_id

Thank you so much for helping me with this

14

no, i mean remove just the parentheses

FROM feeds INNER JOIN timing ON timing.product_id = feeds.product_feed_id INNER JOIN flavour ON flavour.product_id = feeds.product_feed_id INNER JOIN sweeteners ON sweeteners.product_id = feeds.product_feed_id INNER JOIN source ON source.product_id = feeds.product_feed_id INNER JOIN dietary ON dietary.product_id = feeds.product_feed_id INNER JOIN ingredients ON ingredients.product_id = feeds.product_feed_id INNER JOIN product_categories_map ON product_categories_map.product_id = feeds.product_feed_id

15

sorry, this did not make much sense

of far more concern is the fact that these tables you’re joining to the feeds table appear to have multiple rows for each feeds.product_feed_id

hence, i suspect, the need for GROUP BY

therefore, i think this entire query is misguided

16

I’m happy to change the DB structure but I though as there could be dozens of matches on ingredients for each product I thought this was the best way of doing it. If it helps this is the structure

--
-- Table structure for table `dietary`
--

CREATE TABLE IF NOT EXISTS `dietary` (
  `id`,
  `product_id`,
  `dietary`,
  `dietary_id`,
  PRIMARY KEY (`id`)
)

-- --------------------------------------------------------

--
-- Table structure for table `feeds`
--

CREATE TABLE IF NOT EXISTS `feeds` (
  `product_feed_id`,
  `product_name`,
  `product_brand`,
  `brand_name`,
  `delivery_cost`,
  `weight`,
  `weight_id`,
  `price`,
  `serving`,
  `image_url`,
  `product_url`,
  `protein_source`,
  `protein_100`,
  `carbs_100`,
  `fat_100`,
  `calories_100`,
  `type`,
  `protein_from`,
  `next_day`,
  `free_delivery`,
  `stock`,
  `enabled`,
  `deleted`,
  `aw_product_id`,
  `best_seller`,
  PRIMARY KEY (`product_feed_id`)
)

-- --------------------------------------------------------

--
-- Table structure for table `flavour`
--

CREATE TABLE IF NOT EXISTS `flavour` (
  `id`,
  `product_id`,
  `dietary`,
  `flavour_id`,
  PRIMARY KEY (`id`)
)

-- --------------------------------------------------------

--
-- Table structure for table `ingredients`
--

CREATE TABLE IF NOT EXISTS `ingredients` (
  `id`,
  `product_id`,
  `ingredient`,
  `ingredient_id`,
  PRIMARY KEY (`id`)
)

-- --------------------------------------------------------

--
-- Table structure for table `product_categories_map`
--

CREATE TABLE IF NOT EXISTS `product_categories_map` (
  `id`,
  `category_id`,
  `product_id`,
  PRIMARY KEY (`id`)
)

-- --------------------------------------------------------

--
-- Table structure for table `source`
--

CREATE TABLE IF NOT EXISTS `source` (
  `id`,
  `product_id`,
  `dietary`,
  `source_id`,
  PRIMARY KEY (`id`)
)

-- --------------------------------------------------------

--
-- Table structure for table `sweeteners`
--

CREATE TABLE IF NOT EXISTS `sweeteners` (
  `id`,
  `product_id`,
  `sweeteners`,
  `sweeteners_id`,
  PRIMARY KEY (`id`)
)

-- --------------------------------------------------------

--
-- Table structure for table `timing`
--

CREATE TABLE IF NOT EXISTS `timing` (
  `id`,
  `product_id`,
  `dietary`,
  `timing_id`,
  PRIMARY KEY (`id`)
)
17

so you solve this by showing only one ingredient? how does this help?

like i said, it would appear the entire query is misguided

18

CREATE TABLE IF NOT EXISTS `ingredients` ( `id`, `product_id`, `ingredient`, `ingredient_id`, PRIMARY KEY (`id`) )
yeah, this needs to be redesigned

product to ingredient is a many-to-many relationship

this table needs to be normalized

19

Sorry to ask but how do I do that?

20

CREATE TABLE ingredients ( ingredient_id SMALLINT NOT NULL PRIMARY KEY , ingredient VARCHAR(199) NOT NULL ); CREATE TABLE product_ingredients ( product_id INTEGER NOT NULL , ingredient_id SMALLINT NOT NULL , quantity VARCHAR(37) , PRIMARY KEY ( product_id , ingredient_id ) , CONSTRAINT pi_valid_product FOREIGN KEY ( product_id ) REFERENCES products ( product_id ) , CONSTRAINT pi_valid_ingredient FOREIGN KEY ( ingredient_id ) REFERENCES ingredients ( ingredient_id ) );