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
enrolment_number as id, MIN(counsel)
) A
ON = T.counsel_id
GROUP BY T.suitno, T.counsel_id

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

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

Sorry, you can't reply to this topic. It has been closed.

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.