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

  • 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 Mar 17 at 13:18
  • @Vérace thanks for your suggestion, i will do that in next time. – Rhidoy Mar 17 at 14:06
  • @Vérace Here the FOREIGN KEY are match_id, over_id, ball_id, innings_no. – Rhidoy Mar 17 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 Mar 17 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 Mar 17 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.