# Getting max in a join table

**URL:** <https://discourse.cakephp.org/t/getting-max-in-a-join-table/4364>\
**Category:** Uncategorized\
**Created:** [June 1, 2018, 9:03am UTC](https://discourse.cakephp.org/t/getting-max-in-a-join-table/4364 "2018-06-01T09:03:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![wschwarte](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/wschwarte/32/31_2.png) [@wschwarte](https://discourse.cakephp.org/u/wschwarte)\
**Post date:** [June 1, 2018, 9:03am UTC](https://discourse.cakephp.org/t/getting-max-in-a-join-table/4364/1 "2018-06-01T09:03:50Z")

</div>

I have a table users and degrees which is joined with users\_degrees.  
a degree has a level. I need the highest degree per user. How do i do this best/easiest with CakePHP3 ?

```
SELECT user_id, level, degree_name from users_degrees ud 
LEFT JOIN degrees d on ud.degree_id = d.id 
group by applicant_id, degree_id, level, en;    

```

This query will get me the results, still not only the max.

```
user_id level degree_name
1 1 degree_1
1 3 degree_3
1 4 degree_4
2 3 degree_3
2 4 degree_4
3 1 degree_1
3 4 degree_4 

```

i think i need to have max(level) somewhere, but can’t figure out where, and how it would translate to Cake.

---

<div class="post-metadata">

**Author:** ![wschwarte](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/wschwarte/32/31_2.png) [@wschwarte](https://discourse.cakephp.org/u/wschwarte)\
**Post date:** [June 1, 2018, 10:05am UTC](https://discourse.cakephp.org/t/getting-max-in-a-join-table/4364/2 "2018-06-01T10:05:05Z")

</div>

here’s a query that seems to get the results i need, but is this most efficient?  
and how do i transform it to cake3?

```
SELECT d1.level,d1.en from applicants_degrees ad1
left join degrees d1 ON d1.id = ad1.degree_id
WHERE level = (
    SELECT MAX(level) from degrees d2
    left join applicants_degrees ad2 ON d2.id = ad2.degree_id
    where ad1.applicant_id = ad2.applicant_id
);
```

---

<div class="post-metadata">

**Author:** ![Maan](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/maan/32/628_2.png) [@Maan](https://discourse.cakephp.org/u/Maan)\
**Post date:** [June 1, 2018, 7:14pm UTC](https://discourse.cakephp.org/t/getting-max-in-a-join-table/4364/3 "2018-06-01T19:14:53Z")

</div>

you are not using **[BelongsToMany Associations](https://book.cakephp.org/3.0/en/orm/associations.html)** Properly, according to this your middle table should be degrees\_users

> [@wschwarte](#):
>
> users\_degrees

Then if you made all set then you can aceess Required Result by using this queery

> ```
> // In a controller or table method.
> $query = $users->('first', array('conditions' => array('user_id' => $this->uid), 'order'=>'level DESC'))->contain(['Degrees']);
> 
> ```

Hope This will solve you issue 😊 😋