blmg2009 Posted November 22, 2015 Share Posted November 22, 2015 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. Quote Link to comment Share on other sites More sharing options...
benanamen Posted November 22, 2015 Share Posted November 22, 2015 (edited) 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 1 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 22, 2015 Share Posted November 22, 2015 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. 1 Quote Link to comment Share on other sites More sharing options...
blmg2009 Posted November 22, 2015 Author Share Posted November 22, 2015 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. Quote Link to comment Share on other sites More sharing options...
blmg2009 Posted November 22, 2015 Author Share Posted November 22, 2015 ... 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. Quote Link to comment Share on other sites More sharing options...
blmg2009 Posted November 22, 2015 Author Share Posted November 22, 2015 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 22, 2015 Share Posted November 22, 2015 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 1 Quote Link to comment Share on other sites More sharing options...
blmg2009 Posted November 22, 2015 Author Share Posted November 22, 2015 Infact I have added to the first select and now it is showing the NAP Quote Link to comment Share on other sites More sharing options...
blmg2009 Posted November 22, 2015 Author Share Posted November 22, 2015 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_detailsteam_players membersI'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. Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted November 22, 2015 Solution Share Posted November 22, 2015 (edited) 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 1 Quote Link to comment Share on other sites More sharing options...
blmg2009 Posted November 22, 2015 Author Share Posted November 22, 2015 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; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.