0

I am trying to find the rows that exist inside an index, however, my results keep coming up null even though there are rows that match my index. Is there something wrong with the logic of my query?

Here is my test query:

SELECT  `the_products` . * 
FROM  `the_products` 
INNER JOIN  `producttypes_index` ON  `producttypes_index`.`the_product_id` =  `the_products`.`id` 
AND  `producttypes_index`.`type_id` =  '1'
INNER JOIN  `producthashtags_index` ON  `producthashtags_index`.`the_product_id` =  `the_products`.`id` 
WHERE
`producthashtags_index`.`producthashtag_id` 
IN ('41')
AND  
`producthashtags_index`.`producthashtag_id` 
IN ('42')
AND 
`producthashtags_index`.`producthashtag_id` 
IN ('6')
ORDER BY updated_at DESC

Here you can see the_product_id 54433 exists inside the index table producthashtags_index using query:

SELECT * 
FROM  `producthashtags_index` 
WHERE  `the_product_id` =54433

Results:

id      producthashtag_id   the_product_id
25433   6                   54433
25434   41                  54433
25435   42                  54433

Then you can see it also exists inside the index table producttypes_index using query:

SELECT * 
FROM  `producttypes_index` 
WHERE  `the_product_id` =54433

Results:

type_id          the_product_id
1                54433
Kevin J
  • 194
  • 1
  • 11
  • Why do you have 3 `AND IN(...)` together, can't you just say `IN('42','41','6')` with a single statement? Also can you provide a schema so this can be recreated? – R. Chappell Sep 28 '16 at 15:16
  • Because I need all of the rows in the index to exist so '42' AND '41' AND '6' not '42' OR '41' OR '6'. – Kevin J Sep 28 '16 at 15:43
  • I'm not sure why the question was marked a duplicate since the solution is more complicated than the duplicate question which was referenced. – Kevin J Sep 28 '16 at 23:28

1 Answers1

0

Remove 

AND  `producttypes_index`.`type_id` =  '1'

you can also optimaze your code by making the 3 IN`s into one like this:

AND  `producthashtags_index`.`producthashtag_id` IN('42','41','6')
Petko Kostov
  • 367
  • 1
  • 9
  • I need the type_id to be 1 so I can't remove that part. I believe you are correct about the second part. However won't comma separating them make then detect 42 OR 41 OR 6? I need all three to exist in the index. – Kevin J Sep 28 '16 at 15:23
  • Yes it will make them _OR_, if you want them to be do not use _IN_ just use plain old equals sign _=_ Are you sure the _producttypes_index.the_product_id = 51025_ matches any of the _the_product_id`s in the producthashtags_index_. because if not, the _JOIN_ will be empty. – Petko Kostov Sep 28 '16 at 15:35