Error when using mysql sub query

[blmg2009]

Hello, 

 

This is my first attempt at sql sub queries, so please excuse me if it's something very silly. 

 

Below is my sql: 

SELECT td.*, CONCAT(me.First_Name,' ', me.Surname) AS managers_full_name, me.Telephone AS managers_telephone, td.status AS status  
FROM `team_details` td 
JOIN `members` me ON me.id = td.managers_user_id
JOIN (SELECT COUNT(*) AS nap FROM `team_players`) AS tp ON td.team_id = tp.team_id WHERE tp.status = '1'
WHERE td.entry_year = '2016'

The SQL executed fine before I added the second join with a sub query, now I'm getting the following error: 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE td.entry_year = '2016'
LIMIT 0, 25' at line 5 

 

Is this not the correct way of sub querying, or have I missed something? 

 

Any help would be greatly appreciated. 

[benanamen]

You can only have ONE where. Second one should be AND

 

BAD:

 

WHERE tp.status = '1'
WHERE td.entry_year = '2016'

 

GOOD:

 

WHERE tp.status = '1'

AND td.entry_year = '2016'

Edited November 22, 2015 by benanamen

[Barand]

 

ON td.team_id = tp.team_id

 

... and the subquery "tp" doesn't have a team_id to join on. All it has is a "nap" field.

[blmg2009]

You can only have ONE where. Second one should be AND

 

BAD:

 

WHERE tp.status = '1'

WHERE td.entry_year = '2016'

 

GOOD:

 

WHERE tp.status = '1'

AND td.entry_year = '2016'

I thought it would be fine since it was a sub query; thank you for letting me know I will give it ago now.

[blmg2009]

... and the subquery "tp" doesn't have a team_id to join on. All it has is a "nap" field.

My mistake I though the * would be okay, however I can see your point as this will just be the count and not the field `team_id`. 

 

Thank you. 

[blmg2009]

This time it has executed fine: 

 

using: 

SELECT td.*, CONCAT(me.First_Name,' ', me.Surname) AS managers_full_name, me.Telephone AS managers_telephone, td.status AS status  
FROM `team_details` td 
JOIN `members` me ON me.id = td.managers_user_id
JOIN (SELECT team_id, status, COUNT(*) AS nap FROM `team_players`) AS tp ON td.team_id = tp.team_id WHERE tp.status = '1'
AND td.entry_year = '2016'

However I do not see the NAP listed as a column which is the reason for the sub query. 

[Barand]

SELECT td.*, CONCAT(me.First_Name,' ', me.Surname) AS managers_full_name, me.Telephone AS managers_telephone, td.status AS status  

I don't see "nap" in the columns selected by the query (remember your subquery behaves as though it were another (temp) table

[blmg2009]

Infact I have added to the first select and now it is showing the NAP

[blmg2009]

Thank you for your response; 

 

Although I'm not only getting 1 results as if it has grouped it all up; 

 

I have three tables 

 

team_details

team_players 

members

I'm trying to get the NAP which is the 'Number of Accepted Players' which is team_player.status = 1 

The first time I executed my MYSQL in got the full list on teams, their managers name, team name etc. But I need to find how many team players have accepted to join their team.

I thought that the count(*) all team_players that status = 1 for that team_id would provide me this; However it seems I have done this wrong as it's now grouped everything into 1 result. 

 

  
 

[Barand]

If you want the count for each team

JOIN (
    SELECT team_id, status, COUNT(*) AS nap 
    FROM `team_players` 
    WHERE status = '1'
    GROUP BY team_id
) AS tp ON td.team_id = tp.team_id 
    

Edited November 22, 2015 by Barand

[blmg2009]

 

If you want the count for each team

JOIN (
    SELECT team_id, status, COUNT(*) AS nap 
    FROM `team_players` 
    WHERE status = '1'
    GROUP BY team_id
) AS tp ON td.team_id = tp.team_id 
    

 

Thank you very much;