How to have an AND condition in HAVING clause in mysql?

I have 4 tables in a database, namely employee_details_table (emp_no, emp_name, dept_no), 开发者_StackOverflow中文版department_table (dept_no, dept_name), grade_table (grade_id, grade_name) and relation_table (emp_no, grade_id).

The scenario of database structure is: I need to get the emp_name and dept_name of all employees whose (grade_id = 1 AND grade_id = 2 AND grade_id = 3), i.e. employees with all the 3 grade_id. (Note: There can be employees without grade_id.)

My MySQL query is as follows:

SELECT *, GROUP_CONCAT( grade_id ) AS gradeid
  FROM employee_details_table t1
  LEFT JOIN department_table t2
    USING ( dept_no )
  LEFT JOIN relation_table t3
    USING ( emp_no )
  LEFT JOIN grade_table t4
    USING ( grade_id )
  GROUP BY t1.emp_no
  HAVING grade_id = '1' OR grade_id = '2' OR grade_id = '3';

For the above query I get all the rows as output. I also tried using AND instead of OR but I don't get any output. I have used WHERE IN (1,2,3). I get the rows with 1 OR 2 OR 3 as grade_id. WHERE IN(1 AND 2 AND 3) does not work

Please help me correct the query.

Should do the trick:

SELECT *, GROUP_CONCAT( grade_id ) AS gradeid
  FROM employee_details_table t1
  LEFT JOIN department_table t2
    USING ( dept_no )
  LEFT JOIN relation_table t3
    USING ( emp_no )
  LEFT JOIN grade_table t4
    USING ( grade_id )
  WHERE grade_id IN ('1','2','3')
  GROUP BY t1.emp_no
  HAVING COUNT(DISTINCT grade_id) = 3;

First, take all employees that have at least one grade within the interesting ones (1, 2 and 3), then keep only the employees that have exactly three different grades, which indirectly means they have all three of them. If you want your group_concat to have all grades (including those that are not 1, 2 or 3), you'll need to mess around with a subquery, or do some nice magic with an "IF" statement in the HAVING clause. I can give a sample of this if you like.