selecting data

[shaddf]

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 competitionID
and seasonID in table A
 

[requinix]

With a JOIN?

JOIN table a ON table b.SeasonID = table a.SeasonID AND table b.CompetitionID = table a.CompetitionID

That seems a bit too obvious, though. What are you trying to get?

[shaddf]

With a JOIN?

JOIN table a ON table b.SeasonID = table a.SeasonID AND table b.CompetitionID = table a.CompetitionID

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

[shaddf]

+---------+---------------+----------+------+-------------------------------+----------+---------------+-----------------------+
| 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

[Jacques1]

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.

[shaddf]

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?

[requinix]

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