Jump to content
Sign in to follow this  
shaddf

selecting data

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
 

Share this post


Link to post
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?

Share this post


Link to post
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).

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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?

Share this post


Link to post
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...

Share this post


Link to post
Share on other sites

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.

Sign in to follow this  

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