Join mysql table with distinct value from another table-CodePudding

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. However, I keep getting duplicates. This was the mysql 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.

CodePudding user response：

The columns that you want in the results are councel (actually only one of all its values) from cp_counsel and counsel_id from cp_cases_counsel, so you must group by them and select them:

SELECT a.counsel, t.counsel_id, COUNT(*) AS total 
FROM cp_cases_counsel t
INNER JOIN (
  SELECT enrolment_number, MIN(counsel) AS counsel
  FROM cp_counsel
  GROUP BY enrolment_number
) a ON a.enrolment_number = t.counsel_id
GROUP BY a.counsel, t.counsel_id;