Jump to content

selecting data


shaddf

Recommended Posts

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
 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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...
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

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