Joining 3 table

Hi, I need some help.
I want to join 3 tables.
Users, sports and a cross table.
One user can have many sports and I want to search all users by sportsname.
First table is users with
ID INT,
name varchar(),
prename varchar(),

Second table is sports with
ID INT,
sportsname varchar(),

third table is user_sports
ID INT,
users_ID INT,
sports_ID INT,

Waht I want to do is to find all users by searching a sportsname.
Example:
I want to find all users who likes to play golf.
I SQL it will look like:

SELECT users.id, users.name, sports.sportsname
FROM users
INNER JOIN users_sports
on users.id = users_sports.users_id
INNER join sports
on sports.id = users_sports.sports_id
Where sports.sportsname = 'Golf';

But I don’t know how to realize it in cakephp.
I create a own finder method in my UserTable.php

public function findSportsman(SelectQuery $query, $sports)
{
        $columns = ['Users.*'];
        $query = $query
            ->select($columns)
            ->distinct($columns);
        $query->innerJoin('users_sports');
        $query->innerJoin('sports');
}

I am not sure how to set the field refering to the inner join, in SQL it is on column.a = column.b

Or maybe there is another way to use such crossing tables.

Thanks

I’d approach this instead by reading the sport and containing users, it should be much easier. If you absolutely need to do it your way, look up the matching method, that should take care of the joins for you.

hey Zuluru,
thanks for answeriing. Ey it is needed because one user can have many different Sports.
I read the Tutorial and there is same with articles and tags. But I see there no joining.
I will read the matching method. Thanks

I wasn’t suggesting that you change your database structure or table setup, just how you read this particular data.

$sport = $sportTable->findBySportsname('Golf')->contain(['Users'])->first();`

is a quick stab at something that would get you a Sport entity for Golf, which has a property called users which would be an array of all the users who like to play golf.