Jump to content

Error when using mysql sub query


blmg2009
Go to solution Solved by Barand,

Recommended Posts

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. 

Link to comment
Share on other sites

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. :)

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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. 

 

:confused:  
 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.