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”語句做一些好的魔術。如果你願意,我可以給你一個樣本。