SQL Help - Think I need a subquery-CodePudding

I have wrote the following query.

SELECT pro.[Id], COUNT(*) AS Count 
FROM {Task} tsk
JOIN {profile} pro ON tsk.[ProfileId]=pro.[Id]
GROUP BY 
    pro.[Id]
HAVING
    COUNT(*) > 1

This returns the records I am interested in but it returns the following...

ID       Count
12345    3
21254    2
25458    2

I now need to take it a stage further and I think I would need to use the query I have wrote within another query to get what I need.

I basically need to see the underlying data in the count e.g. task-number. So the end result will look something like this based on the above example.

ID       Count
12345    123-345
12345    135-564 
12345    136-985
21254    124-856
21254    135-854
25458    214-854
25458    365-850

Am I correct in thinking I need a subquery to do this and how would I go about it?

Thanks

CodePudding user response：

You could go with a CTE, count, filter then join

WITH CTE AS (
SELECT pro.[Id], COUNT(*) AS Count 
FROM {Task} tsk
JOIN {profile} pro ON tsk.[ProfileId]=pro.[Id]
GROUP BY 
    pro.[Id]
HAVING
    COUNT(*) > 1
)
SELECT tsk.[Id], tsk.[ProfileId] FROM CTE
JOIN {Task} tsk ON CTE.[Id] = tsk.[ProfileId]