Pick records from same table only if exists

Databases
1

In my below query

SELECT * FROM product_details WHERE (product_id =‘141’ or product_id =‘1980’) and date(date_time)>=‘2018-01-01’ group by product_id having count(*)>=2

I want to pick only those records having both product_id = 141 and product_id = 1980 so if product_id 141 is coming 10 times but there is no 1980 it should not be picked.

Any help much appreciated!

2

Sometimes it is simper to expand a Query and to spot why it is not working:

SELECT 
   * 
FROM 
   product_details
WHERE 
   (
      product_id  = '141' 
      OR 
      product_id ='1980'
  ) 
   AND
      date(date_time) >= '2018-01-01' 
GROUP BY
   product_id 
HAVING 
   count(*) >= 2

Have you tried gradually building the query in PhpMyAdmin?

I would try omitting the AND, GROUP BY and HAVING clauses, notice the results then add the clauses one by one.

3

Yes tried but doesn’t achieve my expected results.

4

it should not be picked, but it is – because the count is 10 and that’s greater than 2

remember, you only said HAVING COUNT(*) >= 2

here’s a hint: you want to count distinct products

5

Can i please have this hint in query form.

6

sure

SELECT syntax
  FROM daManual 
 WHERE section = 'Aggregate functions'
/* https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html */
   AND keyword = 'DISTINCT'
7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.