mysql - many to many Query help

Mickah

Not sure if this can be done but here's what I'm trying to do:

I have 3 tables:

tblTeams
team_id
team_name

tblPlayers
player_id
player_name

tblTeamplayerxref
team_id
player_id

tblTeamplayerxref represents a many to many relationship. I want to list all the teams and whether a specific player is a member of that team or not. So a query result I'm looking for:

Arsenal   Null
Tottenham Robbie Keane
Ireland   Robbie Keane

I can't get the resultset to display the teams that a certain player is not a member of, i.e. display null. Rcking my brains through joins

Thanks in advance the help

Using MySQL - 4.1.20-standard, PHP Version 4.4.3.

Adam

If its a new database you're setting up I would recommend going with just two tables, tblPlayer and tblTeam, and put two fields in tblPlayer name something like leagueTeamId and nationalTeamId and populate as you upload the players.

If thats not possible now I'm no use because I can't think straight at this hour, though I think you'll have to use two statements to get your result...

Mickah

Managed to get it running in MSAccess with saved querys.

player_query:

SELECT teamplayerxref.player_id, teamplayerxref.team_id, players.first_name, players.second_name
FROM players INNER JOIN teamplayerxref ON players.player_id = teamplayerxref.player_id
WHERE (((teamplayerxref.player_id)=3));

working query:

SELECT teams.team_name, player_query.first_name, player_query.second_name
FROM teams LEFT JOIN player_query ON teams.team_id = player_query.team_id;

Can't recreate this in mysql, thought creating a view might work but it's not supported with mysql 4.1

Can this be done with joins?

Adam

I'm no *expert* but I severely doubt it...

I'm going to mock up the tables and try but dont get you're hopes up!

Adam

Spent a good hour trying to come up with a solution to that... but like I said I'm no expert so I could be missing something obvious. Anyway if anyone wants it here's the sql that returns the set of results minus the null fields:

$SQL="SELECT * FROM tblTeamPlayerxref INNER JOIN tblPlayers ON xref_player_id = player_id INNER JOIN tblTeams ON xref_team_id = team_id WHERE player_id = '3'";

Also I'm not sure about my previous statement about the database structure, I don't think the one here is ideal but I'm not sure about the one I suggested either...its very late/early...

Mickah

cheers for the attempt fade_to_grey, but that just returns teams player_id=16 is a member of. As in no NULL values.

I can hack it out in PHP, but it means running sub querys in while/if statements, very ugly.

Anyone give me a definitive answer on this?

Seth Brundle

http://forums.devarticles.com/mysql-development-50/many-to-many-sql-query-43843.html

louie

try this
[php]
$sSql = "SELECT `teamplayerxref`.`player_id`, `teamplayerxref`.`team_id`, `players`.`first_name`, `players`.`second_name`
FROM `players`
INNER JOIN `players` ON `teamplayerxref`.`player_id`=`players`.`player_id`
WHERE `teamplayerxref`.`player_id`=3";
[/php]

Kev

Left Join

Serbian

louie wrote:

try this
[php]
$sSql = "SELECT `teamplayerxref`.`player_id`, `teamplayerxref`.`team_id`, `players`.`first_name`, `players`.`second_name`
FROM `players`
INNER JOIN `players` ON `teamplayerxref`.`player_id`=`players`.`player_id`
WHERE `teamplayerxref`.`player_id`=3";
[/php]

This is almost correct, however, you won't get the team names with this query. Also, the players table was joined where the reference table should have been joined instead. In the interest of readablility (well, I find the backticks hard to read personally) I changed the query a little. This should be what you need:

[COLOR="Blue"]SELECT[/COLOR] ref.player_id, tm.team_name, pl.player_name 
  [COLOR="Blue"]FROM[/COLOR] tblPlayers pl
[COLOR="Blue"]INNER JOIN[/COLOR] tblTeamplayerxref ref 
  [COLOR="Blue"]ON[/COLOR] ref.player_id = pl.player_id
[COLOR="Blue"]INNER JOIN[/COLOR] tblTeams tm 
  [COLOR="Blue"]ON[/COLOR] tm.team_id = ref.team_id
[COLOR="Blue"]WHERE[/COLOR] ref.player_id = [COLOR="Red"]3[/COLOR]

Kev wrote:

Left Join

A Left Join actually wouldn't be appropriate in this case unless there are players who have never had a club. If you do want to do something like that, you should just set up a club called Unattached or something like that.

Kev

Serbian wrote:

[COLOR="Blue"]SELECT[/COLOR] ref.player_id, tm.team_name, pl.player_name 
  [COLOR="Blue"]FROM[/COLOR] tblPlayers pl
[COLOR="Blue"]INNER JOIN[/COLOR] tblTeamplayerxref ref 
  [COLOR="Blue"]ON[/COLOR] ref.player_id = pl.player_id
[COLOR="Blue"]INNER JOIN[/COLOR] tblTeams tm 
  [COLOR="Blue"]ON[/COLOR] tm.team_id = ref.team_id
[COLOR="Blue"]WHERE[/COLOR] ref.player_id = [COLOR="Red"]3[/COLOR]

This won't return all the teams unless im not seeing something.

Mickah

Cheers for the help so far...

kbannon: That link is explaining either all member or all NULLs, not both.

Louie: returns just teams player is a member of

Serbian: Only returning teams player is a member of not ones he is not a member of

Gonna mess with LEFT Joins, as Kev suggested.

Serbian

Kev wrote:

This won't return all the teams unless im not seeing something.

Sorry, misread the question. That would return all the teams the player is in, rather than not in. This is actually quite simple if you are looking just for the team names. Try:

[COLOR="Blue"]SELECT[/COLOR] tm.team_name 
  [COLOR="Blue"]FROM[/COLOR] tblTeams tm 
[COLOR="Blue"]INNER JOIN[/COLOR] tblTeamplayerxref ref 
  [COLOR="Blue"]ON[/COLOR] ref.team_id = tm.team_id
[COLOR="Blue"]WHERE[/COLOR] ref.player_id <> [COLOR="Red"]3[/cOLOR]

This wouldn't return teams that have no player in them. You can use the following query to return teams that have no players at all:

[COLOR="Blue"]SELECT[/COLOR] tm.team_name 
  [COLOR="Blue"]FROM[/COLOR] tblTeams tm 
[COLOR="Blue"]WHERE[/COLOR] tm.team_id 
  [COLOR="Blue"]NOT IN[/COLOR] 
   ([COLOR="Blue"]SELECT [/COLOR]team_id 
      [COLOR="Blue"]FROM[/COLOR] tblTeamplayerxref 
    [COLOR="Blue"]WHERE[/COLOR] player_id = 3)

Mickah

serbian,
Is it possible to return ALL teams and whether a player is a member or not, i.e. the resultset in my original post?

Or am I on a wild goose chase?

Kev

Its probably something like this

SELECT team_name, player_name
FROM tblTeams 
LEFT JOIN tblTeamplayerxref 
ON tblTeams.team_id = tblTeamplayerxref.team_id
INNER JOIN tblPlayers
ON tblPlayers.player_id = tblTeamplayerxref.player_id
WHERE tblPlayers.player_id = 3

Mickah

Kev,
Still not returning teams that player is not a member of. This is frustrating as hell.

Serbian,
Is there any way of rolling those two SQL statements into one? I need 1 resultset.

"it's looking a little complicated" - Tell me about it! Thanks tho...

btw subquery ran fine on 4.1

Serbian

Mickah wrote:

Kev,
Still not returning teams that player is not a member of. This is frustrating as hell.

The query I posted above will return teams the player is not a member of:

[COLOR="Blue"]SELECT[/COLOR] tm.team_name 
  [COLOR="Blue"]FROM[/COLOR] tblTeams tm 
[COLOR="Blue"]WHERE[/COLOR] tm.team_id 
  [COLOR="Blue"]NOT IN[/COLOR] 
   ([COLOR="Blue"]SELECT [/COLOR]team_id 
      [COLOR="Blue"]FROM[/COLOR] tblTeamplayerxref 
    [COLOR="Blue"]WHERE[/COLOR] player_id = 3)

I'm not sure if subqueries work in MySQL 4.1 however, so you might have a problem there. I'm looking into getting all teams and whether the player is in the team, but it's looking a little complicated.

Serbian

Mickah wrote:

Is there any way of rolling those two SQL statements into one? I need 1 resultset.

Despite there being two queries, it should still only give one resultset. That whole thing is one SQL statement, I have just broken it up to make it more readable. It's the same as:

[COLOR="Blue"]SELECT[/COLOR] tm.team_name [COLOR="Blue"]FROM[/COLOR] tblTeams tm [COLOR="Blue"]WHERE[/COLOR] tm.team_id [COLOR="Blue"]NOT IN[/COLOR] ([COLOR="Blue"]SELECT [/COLOR]team_id [COLOR="Blue"]FROM[/COLOR] tblTeamplayerxref [COLOR="Blue"]WHERE[/COLOR] player_id = 3)

Mickah

Yeah I understand the subquery, I meant rolling these two statements into one:

SELECT tm.team_name 
  FROM tblTeams tm 
INNER JOIN tblTeamplayerxref ref 
  ON ref.team_id = tm.team_id
WHERE ref.player_id = 3

SELECT tm.team_name 
  FROM tblTeams tm 
WHERE tm.team_id 
  NOT IN 
   (SELECT team_id 
      FROM tblTeamplayerxref 
    WHERE player_id = 3)

Serbian

Mickah wrote:

Yeah I understand the subquery, I meant rolling these two statements into one:

Right sorry, you can union the queries, however, as they are that will just give you a list of every team. Here is the modified query. I have added the member field to tell whether the player is in the team or not:

SELECT tm.team_name, 0 AS member
  FROM tblTeams tm 
WHERE tm.team_id 
  NOT IN 
   (SELECT team_id 
      FROM tblTeamplayerxref 
    WHERE player_id = 1)
UNION 
SELECT tm.team_name, 1 AS member
  FROM tblTeams tm 
INNER JOIN tblTeamplayerxref ref 
  ON ref.team_id = tm.team_id
WHERE ref.player_id = 1

There has to be a better way of doing that :P

Mickah

Serbian you legend, spot on my friend.

Now to the books to figure out your statement!

Kev

Heres my next effort

SELECT t.team_name, p.player_name
FROM tblPlayers p
INNER JOIN tblTeamplayerxref x
ON p.player_id = x.player_id AND p.player_id = 3
RIGHT JOIN tblTeams t
ON t.team_id = x.team_id;

Mickah

Kev,
that returns all teams with every player listed on each team.

basically all rows from players against all rows from teams. Don't worry about it, Serbian sorted it.

Kev

Strange, it worked for me, the "p.player_id = 3" bit should only bring back results for that player.