How to use GROUP BY with SELF JOIN?

I have a person table which holds person and his manager at the same time.

I'm using SELF JOIN to select managers email but I get a lot of duplicates.

http://imageshack.us/photo/my-images/3/withoutgroupby.png

How can I use GROUP BY with my query

SELECT              P.p开发者_如何学运维rs_id AS 'Employee_id', M.prs_id AS 'Manager_id', M.prs_email AS 'Manager_email'
FROM                qrd_prs_person AS P
LEFT OUTER JOIN     qrd_prs_person AS M  
ON                  P.prs_manager_number = M.prs_number

GROUP BY M.prs_id

If I add this line at the end of my query to group by Manager_id, I receive this error

Column 'qrd_prs_person.prs_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm not quite sure what you're trying to achieve?

If you're after a query that returns one row per employee, with two optional columns containing the manager's info, then your original query is correct (without the group by). The relationship is many-to-one, you're starting with a row per "many" that each has a single (optional) "one", so there is no need to group by.

This however is assuming that your data is correct and that prs_number is in fact unique for each employee. If you have two or more managers sharing a prs_number, you will end up with people having multiple managers.

By making this an outer join you're also returning people without a manager (i.e. top of the food chain :)), was this your intention?

EDIT

If you want only managers returned, then you can't keep the first column (P.prs_id) and get one row per manager. If you want the list of people that manage one or more people, this will do the trick:

SELECT              M.prs_id AS 'Manager_id', M.prs_email AS 'Manager_email'
FROM                qrd_prs_person AS P
INNER JOIN          qrd_prs_person AS M  
ON                  P.prs_manager_number = M.prs_number
GROUP BY            M.prs_id, M.prs_email

Why do you want to group by, since I see no Min, Max, Sum or anything that requires grouping ? Maybe an order by would suffice ? Anyway, if you GROUP BY, any column that's not SUMmed or MINed etc MUST be present in the GROUP BY clause.

All your columns in select statement need to be either grouped by or they need to have an aggregate function, you can try adding MAX(qrd_prs_person.prs_id) and see if it works. Should help you :-)

Also you will need to add the email id part in group by part, else use MAX() Your query should be

SELECT  MAX(P.prs_id) AS 'Employee_id', 
        M.prs_id AS 'Manager_id', 
        M.prs_email AS 'Manager_email'
FROM                
    qrd_prs_person AS P
    LEFT OUTER JOIN     
        qrd_prs_person AS M  ONP.prs_manager_number = M.prs_number
GROUP BY 
    M.prs_id, M.prs_email

If you were using MySQL, it would just take one of the M.prs_id values from the group. But these values may be different inside the group, so it doesn't really make sense to just pick a random one of them. That's why SQL Server complains.

I think you just misunderstood how GROUP BY works. What exactly are you trying to achieve with this query?

The problem is that when you do a group by, suddenly you have many possible answers for the contents of P.prs_id AS 'Employee_id' in your select clause. (One for each of the employees of a manager.) SQL Sever wants to know exactly what you want to go there, so it's insisting that you provide some way of totaling up all the values of that into one value.

Probably you want to get a count of employees, so just replace it with COUNT(P.prs_id) AS 'Employee_count'.

You will also need to add M.prs_email to your group by clause.