How to join table with the distinct value from another table

[Mofoluwasho Kolawole Jimoh]

Good day. I encountered a problem on a database I am working with. I have a table of counsels which may hold repeating values, but their is an enrolment number filed which is unique and can be used to fetch them. However, I want to join from a cases_counsel table on the "first" unique value of the counsel table that matches that column on the cases counsel table.

I want to list the cases belonging to a particular counsel using the enrolment_number as the counsel_id on the cp_cases_counsel table. That means I want to pick just a distinct value of a counsel, then use it to join the cp_cases_counsel table and also return the count for such.

I want to have something like

```
John Does Segun SCN1200 2 cases

Situr Grimen SCN100 1 case.

```
However, I keep getting duplicates. This was the query I tried
```

SELECT T.suitno, T.counsel_id, COUNT(*) as total from cp_cases_counsel T
INNER JOIN (SELECT
enrolment_number as id, MIN(counsel)
FROM
cp_counsel
GROUP BY
enrolment_number
) A
ON A.id = T.counsel_id
GROUP BY T.suitno, T.counsel_id
```
and

```
SELECT enrolment_number as id, MIN(counsel) as counsel, COUNT(*) as total FROM cp_counsel
JOIN cp_cases_counsel ON cp_cases_counsel.counsel_id = cp_counsel.enrolment_number
GROUP BY enrolment_number
```
For the second query, it's joining twice and I am having like double of what I am supposed to get.

Thanks for listening to me.