PHP and MYSQL join tables and select users id in both tables.

[blmg2009]

Hello, 

 

I have the following function in my class: 

	public function my_teams() {	
	    $sql = "
		SELECT DISTINCT(td.team_id) AS team_id, td.team_name AS team_name, CONCAT(me.First_Name,' ', me.Surname) AS managers_full_name   
		FROM `team_details` AS td
		JOIN `team_players` AS tp
		JOIN `members` AS me
		ON td.managers_user_id = me.id
		WHERE td.managers_user_id = '" . $this->get_user_id() ."'
		OR tp.member_id = '" . $this->get_user_id() ."'
		";	
		$my_teams = $this->db->fetch_all_array($sql);
		return $my_teams;
	}

I have three tables: 

 

team_details 

 

team_players 

 

members 

 

I'm trying to select all teams for which the USER can be the team manager `managers_user_id` and also a player `member_id` in another managers team. 

 

however it seems that my above code is only fetching the team in which the user is the manager and not the teams which the user is a player in. 

 

Is there another way of doing this?

 

Thank you for reading. 

 

[Barand]

DISTINCT is not a function that you can apply to a single field - it applies to the whole row.

 

You haven't defined the join condition for the tables team_details and team_players.

 

You haven't given us your table structure.

[hansford]

It seems that you are limiting your selection by using the distinct keyword

SELECT DISTINCT(td.team_id)