I have 4 tables in a database, namely employee_details_table (emp_no, emp_name, dept_no), department_table (dept_no, dept_name), grade_table (grade_id, grade_name) and relation_table (emp_no, grade_id).
我在数据库中有4个表,即employee_details_table(emp_no,emp_name,dept_no),department_table(dept_no,dept_name),grade_table(grade_id,grade_name)和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.)
数据库结构的场景是:我需要获得所有员工的emp_name和dept_name(grade_id = 1 AND grade_id = 2 AND grade_id = 3),即拥有所有3 grade_id的员工。 (注意:可能有没有grade_id的员工。)
My MySQL query is as follows:
我的MySQL查询如下:
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
对于上面的查询,我将所有行作为输出。我也尝试使用AND而不是OR,但我没有得到任何输出。我使用过WHERE IN(1,2,3)。我得到1 OR 2 OR 3的行作为grade_id。在(1和2和3)中的哪个不起作用
Please help me correct the query.
请帮我纠正查询。
1 个解决方案
#1
2
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.
首先,将所有至少有一个等级的员工带到有趣的(1,2和3),然后只保留具有三个不同等级的员工,这间接意味着他们拥有所有三个等级。如果你希望你的group_concat拥有所有等级(包括那些不是1,2或3的等级),你需要搞乱一个子查询,或者在HAVING子句中使用“IF”语句做一些好的魔术。如果你愿意,我可以给你一个样本。
#1
2
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.
首先,将所有至少有一个等级的员工带到有趣的(1,2和3),然后只保留具有三个不同等级的员工,这间接意味着他们拥有所有三个等级。如果你希望你的group_concat拥有所有等级(包括那些不是1,2或3的等级),你需要搞乱一个子查询,或者在HAVING子句中使用“IF”语句做一些好的魔术。如果你愿意,我可以给你一个样本。