SQL JOIN

[tebrown]

Im currently working on a new feature called 'Request Player'. Basically what this does is allows managers to request players within their club for a upcoming fixture. There a multiple teams within a club (Senior As, Senior Bs, Colts U21s). Each team is controlled by a manager. And then they can add players as they need.

 

Anyway, I'm having problems with the sql JOIN.

 

I have currently got this:

 

$check = "SELECT u.* FROM users AS u LEFT JOIN requests AS r ON (u.id = r.player_id) WHERE (u.id = r.player_id)";

 

I have got two tables.

 

users - all the managers and players.

requests - all the requests the managers make to other teams within their club.

 

In the request table i have got this:

 

id

player_id - the players that has been requested.

fixture_id - the fixture the manager wants to have that player for.

accepted - if he has been accepted or not. This comes later on.

 

At the moment i have got it working so that it inserts this data, although the code is working but i want to have a mysql query that checks if a manager has any incoming requests from other teams within the club. BUT only request that has been made to his team....

 

Could someone please help me out?

 

[gizmola]

There is no way to provide you what you are asking for, because their is no way given the information you provided for someone to know what requests are intended for a particular manager.  In fact it seems that the request table is missing a key column which would be the requestor_id, as well. 

 

All that table can seem to tell us at the moment is that "someone" made a request for a player, for a particular fixture, and that request has been accepted (or not).  If that's a (1 for yes, 0 for no) then that design is also deficient, as you would want a state to indicate that the request is "pending" and has not yet been accepted or refused.  You could use null for that state, but I think that's poor practice, and would typically have a scheme that involved something like:  0 = Pending (default), 1 = "Rejected", 2 = "Accepted".