MySQL Forums
Forum List  »  Newbie

How to join table with the distinct value from another table
Posted by: Mofoluwasho Kolawole Jimoh
Date: October 26, 2021 12:09AM

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.

Options: ReplyQuote


Subject
Written By
Posted
How to join table with the distinct value from another table
October 26, 2021 12:09AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.