Codeigniter – Return members associated with a specific location – Phpmyadmin

I am building software where a Choir Leader can run multiple Choirs.

I am trying to return all members associated with a specific Choir Group. So when the Choir leader selects a specific Choir Location, they see all members asssociated with that location.

I have a aauth_users table and a choir_locations_to_users table in the MySQL Database.

the aauth_users table holds all the data regarding each choir member:

the choir_locations_to_users table is a seperate table that lists the user ID and the ID of the choir location.

I am performing a query to grab the necessary data I need to display all the members. such as member name, avatar etc to display on the Choir Location page.

The $id parameter in the function is the Choir Location ID.

public function get_choirlocation_members($id) {

        // $query = 'SELECT aauth_users.full_name, aauth_users.avatar, aauth_users.last_login, choir_location_to_users.location_id FROM aauth_users, choir_location_to_users WHERE aauth_users.id = choir_location_to_users.user_id ';
        $this->db->select('aauth_users.full_name, aauth_users.avatar, aauth_users.last_login, aauth_users.id, choir_location_to_users.location_id');
        $this->db->from('aauth_users, choir_location_to_users');
        $this->db->where('aauth_users.id = choir_location_to_users.user_id');
        $this->db->order_by('aauth_users.last_login', 'DESC');
        $members = $this->db->get();

        foreach ($members->result() as $row) {
            if($row->location_id == $id){
                return $members;
            }
        }

    }

The query above returns the following results:

The problem is in the function after the query it seems to be returning all of the members and not just the members related to this location. as when I do another foreach in my View File with the result I still see all members.

foreach ($members->result() as $row): ?>
    <li>
        <img class="profile-user-img img-responsive img-circle" src="<?php echo BASE_URL.'uploads/user/'.$row->avatar ?>" alt="User Image">
        <a class="users-list-name" href="#"><?php echo $row->full_name ?></a>
        <span class="users-list-date">Last Login: <?php echo date ( "d.m.Y " , strtotime ($row->last_login  )); ?></span>
    </li>
    <?php endforeach; ?> 

I would really appreciate any help to point out where I may be going wrong in trying to filter out the query results to show only members associated with a specific Choir location based on the location id.

UPDATE Anwser from @Dinesh Ali not working here is what I have:

I tested the new Join Query you suggested in phpMyAdmin and this is the result:

As you see it is returning the correct data in a result table, which is good, so I added the function you suggested like so:

public function get_choirlocation_members($id) {

    $this->db->select('aauth_users.full_name, aauth_users.avatar, aauth_users.last_login, aauth_users.id, choir_location_to_users.location_id');
    $this->db->from('aauth_users');
    $this->db->join('choir_location_to_users', 'aauth_users.id = choir_location_to_users.user_id');
    $this->db->where('aauth_users.id = choir_location_to_users.user_id');
    $this->db->order_by('aauth_users.last_login', 'DESC');
    $members = $this->db->get()->result();



    if(isset($members) && count($members) > 0){
      return $members;      
    }else{
       return false;
    }  

}

Then in my view File I am using the following to show each user as a LI list item:

  foreach ($members as $row): ?>



        <li>
            <img class="profile-user-img img-responsive img-circle" src="<?php echo BASE_URL.'uploads/user/'.$row->avatar ?>" alt="User Image">
            <a class="users-list-name" href="<?php echo BASE_URL.'administrator/user/view/'.$row->id ?>"><?php echo $row->full_name ?></a>
            <span class="users-list-date">Last Login: <?php echo date ( "d.m.Y " , strtotime ($row->last_login  )); ?></span>
        </li>


<?php endforeach; ?>  

But every user is still showing I have 3 users and I only have one user associated with each location, so I expect to see just one result in the view?