Home / Tricky Select Queries / Question No: 63

How to retrieve only one child row against each parent id even when the child table has multiple matching rows. Multiple rows are like below:

department_id    employee_id
    1         		1
    1         		2
    2         		3
    2		        4
    3		        5
    3         		6
    4         		7
    4         		8
    4         		9
    4         		10

For this MySQL provides GROUP BY which also works even when the SELECT does not specify aggregate function. So the GROUP BY can also be used as follow:

SELECT d.dept_id department_id, e.emp_id AS employee_id  
FROM departments d  INNER JOIN employees e 
ON e.emp_dept_id = d.dept_id  GROUP BY d.dept_id