Resolved: Tips and Tricks for JOINS
Hi Martin,
I ran the query given in the solution of the lesson but found the solution a bit unclear as to what the question was. The query counts the number of males and female but doesn't include the condition of them being "Managers".
I had a different query which gave a more refined result but was technically the same. So the question is why didn't the condition of being a manager did not affect the count of males and females?
The query I used was:
SELECT COUNT(e.emp_no) AS No_of_employees, e.gender,t.title
FROM employees e
JOIN titles t ON e.emp_no = t.emp_no
WHERE t.title = 'Manager'
GROUP BY gender; Also, can you please tell me how to use the syntax of "HAVING" instead of "WHERE" in the above query?
FROM employees e
JOIN titles t ON e.emp_no = t.emp_no
WHERE t.title = 'Manager'
GROUP BY gender; Also, can you please tell me how to use the syntax of "HAVING" instead of "WHERE" in the above query?
1 answers ( 0 marked as helpful)
Hi Archisman!
Thanks for reaching out.
Your suggestion is a valid alternative to the solution we've provided thanks to the assumption (which is correct in our case) that the titles table keeps all records about the managers accurately, as well as that it contains no missing data.
On the other hand, we've made the assumption that the valid records, or the table of reference, should be dept_manager.
However, since our database is well organised, your suggested solution is a valid alternative as well. Thank you for sharing it!
Regarding your second question, WHERE and HAVING are not just substitutes. They have different functionalities and execute different operations.
In this example, you group by a field that is different from the field you are applying a condition to. Therefore, you need WHERE to indicate which records from the relevant join you'll need prior to applying any grouping.
Hope this helps.
Best,
Martin
Best,
Martin