shaddf Posted July 30, 2017 Share Posted July 30, 2017 i have this table A +---------+---------------+----------+ | MatchID | CompetitionID | SeasonID | +---------+---------------+----------+ | 27 | 8 | 12 | | 28 | 10 | 12 | +---------+---------------+----------+ 2 rows in set (0.00 sec) and this table B +------+-------------------------------+----------+---------------+------------------------+ | Yr | Achievement | SeasonID | CompetitionID | CompetitionName | +------+-------------------------------+----------+---------------+------------------------+ | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 2016 | UN Peace Cup Winners | 12 | 9 | UN Peace Cup | | 2016 | Kyanja U-14 Cup Winners | 12 | 10 | KYANJA U-14 Cup | | 2016 | NDIV V Winners | 12 | 7 | Nakawa Fifth division | | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 2015 | UN Peace Cup Winners | 13 | 9 | UN Peace Cup | | 2015 | Kyanja U-14 Cup Winners | 13 | 10 | KYANJA U-14 Cup | | 2015 | NDIV V Winners | 13 | 7 | Nakawa Fifth division | +------+-------------------------------+----------+---------------+------------------------+ 8 rows in set (0.02 sec) how can i select a competitionId from table B corresponding to a group of matches with same competitionIDand seasonID in table A Quote Link to comment https://forums.phpfreaks.com/topic/304447-selecting-data/ Share on other sites More sharing options...
requinix Posted July 30, 2017 Share Posted July 30, 2017 With a JOIN? JOIN table a ON table b.SeasonID = table a.SeasonID AND table b.CompetitionID = table a.CompetitionIDThat seems a bit too obvious, though. What are you trying to get? Quote Link to comment https://forums.phpfreaks.com/topic/304447-selecting-data/#findComment-1549050 Share on other sites More sharing options...
shaddf Posted July 30, 2017 Author Share Posted July 30, 2017 With a JOIN? JOIN table a ON table b.SeasonID = table a.SeasonID AND table b.CompetitionID = table a.CompetitionIDThat seems a bit too obvious, though. What are you trying to get? +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ | MatchID | CompetitionID | SeasonID | Yr | Achievement | SeasonID | CompetitionID | CompetitionName | +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ | 27 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 28 | 10 | 12 | 2016 | Kyanja U-14 Cup Winners | 12 | 10 | KYANJA U-14 Cup | | 27 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ matchID 27 row has been repeated this should not be happeningfor the same season(it is like the match was played twice). Quote Link to comment https://forums.phpfreaks.com/topic/304447-selecting-data/#findComment-1549072 Share on other sites More sharing options...
shaddf Posted July 30, 2017 Author Share Posted July 30, 2017 +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ | MatchID | CompetitionID | SeasonID | Yr | Achievement | SeasonID | CompetitionID | CompetitionName | +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ | 27 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 28 | 10 | 12 | 2016 | Kyanja U-14 Cup Winners | 12 | 10 | KYANJA U-14 Cup | | 27 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ matchID 27 row has been repeated this should not be happeningfor the same season(it is like the match was played twice). Also +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ | MatchID | CompetitionID | SeasonID | Yr | Achievement | SeasonID | CompetitionID | CompetitionName | +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ | 27 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 29 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 28 | 10 | 12 | 2016 | Kyanja U-14 Cup Winners | 12 | 10 | KYANJA U-14 Cup | | 27 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 29 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ 5 rows in set (0.00 sec) And also if i have other matches falling under a specific CompetitionID in same season this collection should come as one row with a count of 1/2 depending on season in which they are Quote Link to comment https://forums.phpfreaks.com/topic/304447-selecting-data/#findComment-1549073 Share on other sites More sharing options...
Jacques1 Posted July 30, 2017 Share Posted July 30, 2017 matchID 27 row has been repeated Yeah, because this is exactly how you've stored it: +------+-------------------------------+----------+---------------+------------------------+ | Yr | Achievement | SeasonID | CompetitionID | CompetitionName | +------+-------------------------------+----------+---------------+------------------------+ | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | <---- | 2016 | UN Peace Cup Winners | 12 | 9 | UN Peace Cup | | 2016 | Kyanja U-14 Cup Winners | 12 | 10 | KYANJA U-14 Cup | | 2016 | NDIV V Winners | 12 | 7 | Nakawa Fifth division | | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | <---- | 2015 | UN Peace Cup Winners | 13 | 9 | UN Peace Cup | | 2015 | Kyanja U-14 Cup Winners | 13 | 10 | KYANJA U-14 Cup | | 2015 | NDIV V Winners | 13 | 7 | Nakawa Fifth division | +------+-------------------------------+----------+---------------+------------------------+ If that's an error (which it looks like), then you need to repair your data and table definitions to prevent this from happening again. Quote Link to comment https://forums.phpfreaks.com/topic/304447-selecting-data/#findComment-1549074 Share on other sites More sharing options...
shaddf Posted July 31, 2017 Author Share Posted July 31, 2017 Yeah, because this is exactly how you've stored it: +------+-------------------------------+----------+---------------+------------------------+ | Yr | Achievement | SeasonID | CompetitionID | CompetitionName | +------+-------------------------------+----------+---------------+------------------------+ | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | <---- | 2016 | UN Peace Cup Winners | 12 | 9 | UN Peace Cup | | 2016 | Kyanja U-14 Cup Winners | 12 | 10 | KYANJA U-14 Cup | | 2016 | NDIV V Winners | 12 | 7 | Nakawa Fifth division | | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | <---- | 2015 | UN Peace Cup Winners | 13 | 9 | UN Peace Cup | | 2015 | Kyanja U-14 Cup Winners | 13 | 10 | KYANJA U-14 Cup | | 2015 | NDIV V Winners | 13 | 7 | Nakawa Fifth division | +------+-------------------------------+----------+---------------+------------------------+ If that's an error (which it looks like), then you need to repair your data and table definitions to prevent this from happening again. Ok then Also +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ | MatchID | CompetitionID | SeasonID | Yr | Achievement | SeasonID | CompetitionID | CompetitionName | +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ | 27 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 29 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 28 | 10 | 12 | 2016 | Kyanja U-14 Cup Winners | 12 | 10 | KYANJA U-14 Cup | | 27 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | | 29 | 8 | 12 | 2016 | GO BACK TO SCHOOL Cup Winners | 12 | 8 | GO BACK TO SCHOOL Cup | +---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+ 5 rows in set (0.00 sec) if i have other matches falling under a specific CompetitionID in same season this collection should come as one row with a count of 1/2 depending on season in which they are,how can I achieve this? Quote Link to comment https://forums.phpfreaks.com/topic/304447-selecting-data/#findComment-1549076 Share on other sites More sharing options...
requinix Posted July 31, 2017 Share Posted July 31, 2017 So instead of the MatchID you want a count of the number of matches per competition+season? First, it feels to me like one of the 2016 "GO BACK TO SCHOOL Cup Winners" duplicates should actually be in 2015 instead. Second, what you're saying doesn't make sense. Why are there multiple rows in table A with the same CompetitionID and SeasonID but different MatchIDs? There being multiple matches in a competition makes sense, but you asking for a count of the number of matches doesn't because it will be the same number for a given SeasonID+CompetitionID with all achievements. But whatever. If you want to count the number of matches then do the JOIN using a subquery instead of the original table: JOIN (SELECT CompetitionID, SeasonID, COUNT(1) AS MatchCount FROM table a GROUP BY CompetitionID, SeasonID) a ON... Quote Link to comment https://forums.phpfreaks.com/topic/304447-selecting-data/#findComment-1549077 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.