0

I have Three Table, One Master Table and Other Two Table have relation with it.

Below is the Three Table:

1. ball_by_ball table, this table holds full information by ball by ball:

CREATE TABLE `ball_by_ball` (
  `match_id` int(11) NOT NULL,
  `over_id` int(3) NOT NULL,
  `ball_id` int(2) NOT NULL,
  `innings_no` int(1) NOT NULL,
  `team_batting` int(11) NOT NULL,
  `team_bowling` int(11) NOT NULL,
  `striker_batting_position` int(2) NOT NULL,
  `striker` int(11) NOT NULL,
  `non_striker` int(11) NOT NULL,
  `bowler` int(11) NOT NULL,
  PRIMARY KEY (`match_id`,`over_id`,`ball_id`,`innings_no`),
  KEY `Ball_by_Ball_Ball_Id_index` (`ball_id`),
  KEY `Ball_by_Ball_Innings_No_index` (`innings_no`),
  KEY `Ball_by_Ball_Match_Id_Over_Id_Ball_Id_Innings_No_index` (`match_id`,`over_id`,`ball_id`,`innings_no`),
  KEY `Ball_by_Ball_Match_Id_index` (`match_id`),
  KEY `Ball_by_Ball_Over_Id_index` (`over_id`),
  KEY `Bowler` (`bowler`),
  KEY `Non_Striker` (`non_striker`),
  KEY `Striker` (`striker`),
  KEY `Team_Batting` (`team_batting`),
  KEY `Team_Bowling` (`team_bowling`),
  CONSTRAINT `ball_by_ball_ibfk_1` FOREIGN KEY (`non_striker`) REFERENCES `Player` (`Player_Id`),
  CONSTRAINT `ball_by_ball_ibfk_2` FOREIGN KEY (`striker`) REFERENCES `Player` (`Player_Id`),
  CONSTRAINT `ball_by_ball_ibfk_3` FOREIGN KEY (`match_id`) REFERENCES `match` (`match_id`),
  CONSTRAINT `ball_by_ball_ibfk_4` FOREIGN KEY (`team_batting`) REFERENCES `Team` (`Team_Id`),
  CONSTRAINT `ball_by_ball_ibfk_5` FOREIGN KEY (`team_bowling`) REFERENCES `Team` (`Team_Id`),
  CONSTRAINT `ball_by_ball_ibfk_6` FOREIGN KEY (`bowler`) REFERENCES `Player` (`Player_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. batsman_scored, this table holds batsman run scores by ball by ball:

CREATE TABLE `batsman_scored` (
  `match_id` int(11) NOT NULL,
  `over_id` int(3) NOT NULL,
  `ball_id` int(2) NOT NULL,
  `innings_no` int(1) NOT NULL,
  `runs_scored` int(1) NOT NULL,
  PRIMARY KEY (`match_id`,`over_id`,`ball_id`,`innings_no`),
  KEY `Batsman_Scored_Ball_Id_index` (`ball_id`),
  KEY `Batsman_Scored_Innings_No_index` (`innings_no`),
  KEY `Batsman_Scored_Match_Id_Over_Id_Ball_Id_Innings_No_index` (`match_id`,`over_id`,`ball_id`,`innings_no`),
  KEY `Batsman_Scored_Match_Id_index` (`match_id`),
  KEY `Batsman_Scored_Over_Id_index` (`over_id`),
  KEY `Runs_Scored` (`runs_scored`),
  CONSTRAINT `batsman_scored_ibfk_1` FOREIGN KEY (`match_id`) REFERENCES `ball_by_ball` (`match_id`),
  CONSTRAINT `batsman_scored_ibfk_2` FOREIGN KEY (`over_id`) REFERENCES `ball_by_ball` (`over_id`),
  CONSTRAINT `batsman_scored_ibfk_3` FOREIGN KEY (`ball_id`) REFERENCES `ball_by_ball` (`ball_id`),
  CONSTRAINT `batsman_scored_ibfk_4` FOREIGN KEY (`innings_no`) REFERENCES `ball_by_ball` (`innings_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

3. wicket_taken, this table hold wicket fallen information by ball by ball:

CREATE TABLE `wicket_taken` (
  `match_id` int(11) NOT NULL,
  `over_id` int(3) NOT NULL,
  `ball_id` int(2) NOT NULL,
  `innings_no` int(1) NOT NULL,
  `player_out` int(11) NOT NULL,
  `kind_out` int(2) NOT NULL,
  `fielders` int(11) DEFAULT NULL,
  PRIMARY KEY (`match_id`,`over_id`,`ball_id`,`innings_no`),
  KEY `Ball_Id` (`ball_id`),
  KEY `Fielders` (`fielders`),
  KEY `Innings_No` (`innings_no`),
  KEY `Kind_Out` (`kind_out`),
  KEY `Over_Id` (`over_id`),
  KEY `Player_Out` (`player_out`),
  CONSTRAINT `wicket_taken_ibfk_1` FOREIGN KEY (`player_out`) REFERENCES `Player` (`Player_Id`),
  CONSTRAINT `wicket_taken_ibfk_13` FOREIGN KEY (`over_id`) REFERENCES `ball_by_ball` (`over_id`),
  CONSTRAINT `wicket_taken_ibfk_14` FOREIGN KEY (`fielders`) REFERENCES `Player` (`Player_Id`),
  CONSTRAINT `wicket_taken_ibfk_15` FOREIGN KEY (`ball_id`) REFERENCES `ball_by_ball` (`ball_id`),
  CONSTRAINT `wicket_taken_ibfk_19` FOREIGN KEY (`kind_out`) REFERENCES `Out_Type` (`Out_Id`),
  CONSTRAINT `wicket_taken_ibfk_4` FOREIGN KEY (`match_id`) REFERENCES `ball_by_ball` (`match_id`),
  CONSTRAINT `wicket_taken_ibfk_7` FOREIGN KEY (`innings_no`) REFERENCES `ball_by_ball` (`innings_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

This is my Query for joining three table:

select *
from ball_by_ball a
       left join batsman_scored b using (match_id, over_id, ball_id, innings_no)
       left join wicket_taken wt using (match_id, over_id, ball_id, innings_no)
where a.match_id = 981018
  and a.innings_no = 1;

Output: (First 24 Rows)

+----------+---------+---------+------------+--------------+--------------+--------------------------+---------+-------------+--------+-------------+------------+----------+----------+
| match_id | over_id | ball_id | innings_no | team_batting | team_bowling | striker_batting_position | striker | non_striker | bowler | runs_scored | player_out | kind_out | fielders |
+----------+---------+---------+------------+--------------+--------------+--------------------------+---------+-------------+--------+-------------+------------+----------+----------+
|   981018 |       1 |       1 |          1 |           13 |            2 |                        1 |     254 |           2 |    270 |           0 |       NULL |     NULL |     NULL |
|   981018 |       1 |       2 |          1 |           13 |            2 |                        1 |     254 |           2 |    270 |           0 |       NULL |     NULL |     NULL |
|   981018 |       1 |       3 |          1 |           13 |            2 |                        1 |     254 |           2 |    270 |           0 |       NULL |     NULL |     NULL |
|   981018 |       1 |       4 |          1 |           13 |            2 |                        2 |       2 |         254 |    270 |           0 |       NULL |     NULL |     NULL |
|   981018 |       1 |       5 |          1 |           13 |            2 |                        2 |       2 |         254 |    270 |           0 |       NULL |     NULL |     NULL |
|   981018 |       1 |       6 |          1 |           13 |            2 |                        2 |       2 |         254 |    270 |           1 |       NULL |     NULL |     NULL |
|   981018 |       2 |       1 |          1 |           13 |            2 |                        2 |       2 |         254 |    140 |           0 |          2 |        1 |      110 |
|   981018 |       2 |       2 |          1 |           13 |            2 |                        1 |     254 |          21 |    140 |           4 |       NULL |     NULL |     NULL |
|   981018 |       2 |       3 |          1 |           13 |            2 |                        1 |     254 |          21 |    140 |           0 |       NULL |     NULL |     NULL |
|   981018 |       2 |       4 |          1 |           13 |            2 |                        1 |     254 |          21 |    140 |           0 |        254 |        1 |      162 |
|   981018 |       2 |       5 |          1 |           13 |            2 |                        3 |      88 |          21 |    140 |           1 |       NULL |     NULL |     NULL |
|   981018 |       2 |       6 |          1 |           13 |            2 |                        4 |      21 |          88 |    140 |           0 |       NULL |     NULL |     NULL |
|   981018 |       3 |       1 |          1 |           13 |            2 |                        3 |      88 |          21 |    270 |           1 |       NULL |     NULL |     NULL |
|   981018 |       3 |       2 |          1 |           13 |            2 |                        4 |      21 |          88 |    270 |           0 |       NULL |     NULL |     NULL |
|   981018 |       3 |       3 |          1 |           13 |            2 |                        4 |      21 |          88 |    270 |           0 |       NULL |     NULL |     NULL |
|   981018 |       3 |       4 |          1 |           13 |            2 |                        4 |      21 |          88 |    270 |           0 |       NULL |     NULL |     NULL |
|   981018 |       3 |       5 |          1 |           13 |            2 |                        4 |      21 |          88 |    270 |           1 |       NULL |     NULL |     NULL |
|   981018 |       3 |       6 |          1 |           13 |            2 |                        3 |      88 |          21 |    270 |           0 |       NULL |     NULL |     NULL |
|   981018 |       4 |       1 |          1 |           13 |            2 |                        4 |      21 |          88 |     32 |           0 |       NULL |     NULL |     NULL |
|   981018 |       4 |       2 |          1 |           13 |            2 |                        4 |      21 |          88 |     32 |           0 |       NULL |     NULL |     NULL |
|   981018 |       4 |       3 |          1 |           13 |            2 |                        4 |      21 |          88 |     32 |           0 |       NULL |     NULL |     NULL |
|   981018 |       4 |       4 |          1 |           13 |            2 |                        4 |      21 |          88 |     32 |           0 |         21 |        1 |      270 |
|   981018 |       4 |       5 |          1 |           13 |            2 |                        5 |     147 |          88 |     32 |           0 |       NULL |     NULL |     NULL |
|   981018 |       4 |       6 |          1 |           13 |            2 |                        5 |     147 |          88 |     32 |           1 |       NULL |     NULL |     NULL |

I want to sum run_scored row and group those by player_out. Which is actually called Fall Of Wicket from Cricket View. More easily i want to know which wicket had fallen and that time what was the score which i will get by summing all the run_scored row until player_out row value is NOT NULL. I can do that by using loop and arrays where i will change the sum value when there will be no NULL value in player_out row but i don't know how can do that using MySQL query.

My Require Output Will Be Like Below:

player_out `SUM(run_score)`
2   1
254 4
21  3
.
.

So how can i do this.?

13
  • Please do not use screenshots on StackExchange. Set up a fiddle (e.g. dbfiddle.uk) and you will likely get good/better results. Also, the screenshots don't demonstrate the FOREIGN KEY relationships between the tables - they would be very helpful!
    – Vérace
    Commented Mar 17, 2019 at 13:18
  • @Vérace thanks for your suggestion, i will do that in next time.
    – Rhidoy
    Commented Mar 17, 2019 at 14:06
  • @Vérace Here the FOREIGN KEY are match_id, over_id, ball_id, innings_no.
    – Rhidoy
    Commented Mar 17, 2019 at 14:15
  • Great. What you could do is to replace your screenshots with the output of SHOW CREATE TABLE my_table\G; for the three tables - that would be a start helping others to help you! This you can do easily from the mysql CLI programme. I'm fairly sure you can do it from a GUI also, but please post the results as text - which users can then copy/paste into whatever tool they wish to use. You can edit your own posts at all times. See the Questions: section of my profile here for tips on how to ask questions.
    – Vérace
    Commented Mar 17, 2019 at 15:17
  • 1
    @Rhidoy - Now I am puzzled. I see two tables with identical PRIMARY KEYs. What's the rationale? Normally, one would have a single table.
    – Rick James
    Commented Mar 17, 2019 at 21:15

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.