#1066 - Not unique table/alias

I'm getting the following error on the below query. Can anyone see why?

#1066 - Not unique table/alias: 'MatchPlayerBatting'

SELECT (Balls / Wickets) AS StrikeRate,
          (Runs / Balls * 6) AS EconomyRate,
          PlayerID,
          Balls,
          Matches,
          Innings,
          Runs,
          Wickets,
          MaxSumBowlWickets,
          Average,
          FourWicketBags,
          FiveWicketBags
   FROM
     (SELECT PlayerID AS PlayerID,
             SUM(IF(instr(BowlOvers, '.') != '', SUBSTR(BowlOvers, 1, instr(BowlOvers, '.') - 1), BowlOvers) * IF(Notes = '5-ball overs', 5, IF(Notes = '8-ball overs', 8, IF(Notes = '', 6, 0))) + IF(instr(BowlOvers, '.') != '', SUBSTR(BowlOvers, instr(BowlOvers, '.') + 1), 0)) AS Balls,
             COUNT(DISTINCT MatchID) AS Matches,
             COUNT(DISTINCT MatchPlayerBowlingIDAuto) AS Innings,
             SUM(BowlRuns) AS Runs,
             SUM(BowlWickets) AS Wickets,
             MAX(BowlWickets) AS MaxSumBowlWickets,
             (SUM(BowlRuns) / SUM(BowlWickets)) AS Average,
             COUNT(CASE WHEN BowlWickets = 4 THEN PlayerID end) AS FourWicketBags,
             COUNT(CASE WHEN BowlWickets >= 5 THEN PlayerID end) AS FiveWicketBags
      FROM
        (SELECT DISTINCT MatchPlayerBowling.PlayerID AS PlayerID,
                         BowlOvers,
                         Notes,
                         MatchPlayerResult.MatchID,
                         MatchPlayerBowling.MatchPlayerBowlingIDAuto,
                         BowlRuns,
                         BowlWickets
         FROM MatchPlayerResult
         INNER JOIN Fixture ON MatchPlayerResult.MatchID = Fixture.MatchID
         INNER JOIN MatchPlayerBowling ON MatchPlayerResult.MatchID = MatchPlayerBowling.MatchID
         AND MatchPlayerResult.MatchType = MatchPlayerBowling.MatchType
         AND MatchPlayerResult.PlayerID = MatchPlayerBowling.PlayerId
         INNER JOIN Player ON MatchPlayerBowling.PlayerID = Player.PlayerId
         AND MatchPlayerBowling.MatchID in (275,
                                            728,
                                            729,
                                            730,
                                            733,
                                            735,
                                            736,
                                            796,
                                            798,
                                            887,
                                            889,
                                            891,
                                            922,
                                            924,
                                            926,
                                            1029,
                                            1030,
                                            1031,
                                            1035,
                                            1038,
                                            1040,
                                            1082,
                                            1084,
                                            1087,
                                            1129,
                                            1141,
                                            1215,
                                            1216,
                                            1217,
                                            1235,
                                            1236,
                                            1238,
                                            1383,
                                            1386,
                                            1388,
                                            1488,
                                            1491,
                                            1493,
                                            1565,
                                            1571,
                                            1573,
                                            1721,
                                            1723,
                                            1744,
                                            1742,
                                            1739,
                                            1894,
                                            1896,
                                            1955,
                                            1957,
                                            2021,
                                            2020)
         AND MatchPlayerBowling.MatchType = 'Test'
         AND Fixture.MatchType = 'Test'
         INNER JOIN MatchPlayerBatting ON MatchPlayerResult.MatchID = MatchPlayerBatting.MatchID
         AND MatchPlayerResult.MatchType = MatchPlayerBatting.MatchType
         AND MatchPlayerResult.PlayerID = MatchPlayerBatting.PlayerCommonName
         INNER JOIN MatchPlayerBatting ON MatchPlayerBowling.MatchID = MatchPlayerBatting.MatchID
         AND MatchPlayerBowling.MatchType = MatchPlayerBatting.MatchType
         AND MatchPlayerBowling.PlayerID = MatchPlayerBatting.BatBowlerCommonName
         AND (TeamOne in ('New Zealand')
              OR TeamTwo in ('New Zealand')
              OR TeamThree in ('New Zealand')))
      GROUP BY MatchPlayerBowling.PlayerID) T) T1) Q2

Open in new window

PeterErhardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Ref line 14 you are aliasing a table using its own name.
I.e. Let PeterErhard be known as PeterErhard
The. You redefine a column within a select as PeterErhard (on line 25)

Likely the first instance is the cause.
You may have it elsewhere, but this might be the first and the last.
0
PeterErhardAuthor Commented:
Thanks arnold, but I don't get what you mean.

Line 14 is:

(SELECT PlayerID AS PlayerID,

and Line 25 is:

 (SELECT DISTINCT MatchPlayerBowling.PlayerID AS PlayerID,

I don't understand how this is causing:

#1066 - Not unique table/alias: 'MatchPlayerBatting'

i.e.

this is what I have now and still get the same error:

SELECT (Balls / Wickets) AS StrikeRate,
          (Runs / Balls * 6) AS EconomyRate,
          PlayerID,
          Balls,
          Matches,
          Innings,
          Runs,
          Wickets,
          MaxSumBowlWickets,
          Average,
          FourWicketBags,
          FiveWicketBags
   FROM
     (SELECT PlayerID,
             SUM(IF(instr(BowlOvers, '.') != '', SUBSTR(BowlOvers, 1, instr(BowlOvers, '.') - 1), BowlOvers) * IF(Notes = '5-ball overs', 5, IF(Notes = '8-ball overs', 8, IF(Notes = '', 6, 0))) + IF(instr(BowlOvers, '.') != '', SUBSTR(BowlOvers, instr(BowlOvers, '.') + 1), 0)) AS Balls,
             COUNT(DISTINCT MatchID) AS Matches,
             COUNT(DISTINCT MatchPlayerBowlingIDAuto) AS Innings,
             SUM(BowlRuns) AS Runs,
             SUM(BowlWickets) AS Wickets,
             MAX(BowlWickets) AS MaxSumBowlWickets,
             (SUM(BowlRuns) / SUM(BowlWickets)) AS Average,
             COUNT(CASE WHEN BowlWickets = 4 THEN PlayerID end) AS FourWicketBags,
             COUNT(CASE WHEN BowlWickets >= 5 THEN PlayerID end) AS FiveWicketBags
      FROM
        (SELECT DISTINCT MatchPlayerBowling.PlayerID,
                         BowlOvers,
                         Notes,
                         MatchPlayerResult.MatchID,
                         MatchPlayerBowling.MatchPlayerBowlingIDAuto,
                         BowlRuns,
                         BowlWickets
         FROM MatchPlayerResult
         INNER JOIN Fixture ON MatchPlayerResult.MatchID = Fixture.MatchID
         INNER JOIN MatchPlayerBowling ON MatchPlayerResult.MatchID = MatchPlayerBowling.MatchID
         AND MatchPlayerResult.MatchType = MatchPlayerBowling.MatchType
         AND MatchPlayerResult.PlayerID = MatchPlayerBowling.PlayerId
         INNER JOIN Player ON MatchPlayerBowling.PlayerID = Player.PlayerId
         AND MatchPlayerBowling.MatchID in (275,
                                            728,
                                            729,
                                            730,
                                            733,
                                            735,
                                            736,
                                            796,
                                            798,
                                            887,
                                            889,
                                            891,
                                            922,
                                            924,
                                            926,
                                            1029,
                                            1030,
                                            1031,
                                            1035,
                                            1038,
                                            1040,
                                            1082,
                                            1084,
                                            1087,
                                            1129,
                                            1141,
                                            1215,
                                            1216,
                                            1217,
                                            1235,
                                            1236,
                                            1238,
                                            1383,
                                            1386,
                                            1388,
                                            1488,
                                            1491,
                                            1493,
                                            1565,
                                            1571,
                                            1573,
                                            1721,
                                            1723,
                                            1744,
                                            1742,
                                            1739,
                                            1894,
                                            1896,
                                            1955,
                                            1957,
                                            2021,
                                            2020)
         AND MatchPlayerBowling.MatchType = 'Test'
         AND Fixture.MatchType = 'Test'
         INNER JOIN MatchPlayerBatting ON MatchPlayerResult.MatchID = MatchPlayerBatting.MatchID
         AND MatchPlayerResult.MatchType = MatchPlayerBatting.MatchType
         AND MatchPlayerResult.PlayerID = MatchPlayerBatting.PlayerCommonName
         INNER JOIN MatchPlayerBatting ON MatchPlayerBowling.MatchID = MatchPlayerBatting.MatchID
         AND MatchPlayerBowling.MatchType = MatchPlayerBatting.MatchType
         AND MatchPlayerBowling.PlayerID = MatchPlayerBatting.BatBowlerCommonName
         AND (TeamOne in ('New Zealand')
              OR TeamTwo in ('New Zealand')
              OR TeamThree in ('New Zealand'))) T)
   GROUP BY MatchPlayerBowling.PlayerID) T1 ) Q2

Open in new window

0
PeterErhardAuthor Commented:
LOL, I noticed I had the same join statement twice for MatchPlayerBatting.

Now however, I get this.

#1248 - Every derived table must have its own alias

SELECT (Balls / Wickets) AS StrikeRate,
          (Runs / Balls * 6) AS EconomyRate,
          PlayerID,
          Balls,
          Matches,
          Innings,
          Runs,
          Wickets,
          MaxSumBowlWickets,
          Average,
          FourWicketBags,
          FiveWicketBags
   FROM
     (SELECT PlayerID,
             SUM(IF(instr(BowlOvers, '.') != '', SUBSTR(BowlOvers, 1, instr(BowlOvers, '.') - 1), BowlOvers) * IF(Notes = '5-ball overs', 5, IF(Notes = '8-ball overs', 8, IF(Notes = '', 6, 0))) + IF(instr(BowlOvers, '.') != '', SUBSTR(BowlOvers, instr(BowlOvers, '.') + 1), 0)) AS Balls,
             COUNT(DISTINCT MatchID) AS Matches,
             COUNT(DISTINCT MatchPlayerBowlingIDAuto) AS Innings,
             SUM(BowlRuns) AS Runs,
             SUM(BowlWickets) AS Wickets,
             MAX(BowlWickets) AS MaxSumBowlWickets,
             (SUM(BowlRuns) / SUM(BowlWickets)) AS Average,
             COUNT(CASE WHEN BowlWickets = 4 THEN PlayerID end) AS FourWicketBags,
             COUNT(CASE WHEN BowlWickets >= 5 THEN PlayerID end) AS FiveWicketBags
      FROM
        (SELECT DISTINCT MatchPlayerBowling.PlayerID,
                         BowlOvers,
                         Notes,
                         MatchPlayerResult.MatchID,
                         MatchPlayerBowling.MatchPlayerBowlingIDAuto,
                         BowlRuns,
                         BowlWickets
         FROM MatchPlayerResult
         INNER JOIN Fixture ON MatchPlayerResult.MatchID = Fixture.MatchID
         INNER JOIN MatchPlayerBowling ON MatchPlayerResult.MatchID = MatchPlayerBowling.MatchID
         AND MatchPlayerResult.MatchType = MatchPlayerBowling.MatchType
         AND MatchPlayerResult.PlayerID = MatchPlayerBowling.PlayerId
         INNER JOIN Player ON MatchPlayerBowling.PlayerID = Player.PlayerId
         AND MatchPlayerBowling.MatchID in (275,
                                            728,
                                            729,
                                            730,
                                            733,
                                            735,
                                            736,
                                            796,
                                            798,
                                            887,
                                            889,
                                            891,
                                            922,
                                            924,
                                            926,
                                            1029,
                                            1030,
                                            1031,
                                            1035,
                                            1038,
                                            1040,
                                            1082,
                                            1084,
                                            1087,
                                            1129,
                                            1141,
                                            1215,
                                            1216,
                                            1217,
                                            1235,
                                            1236,
                                            1238,
                                            1383,
                                            1386,
                                            1388,
                                            1488,
                                            1491,
                                            1493,
                                            1565,
                                            1571,
                                            1573,
                                            1721,
                                            1723,
                                            1744,
                                            1742,
                                            1739,
                                            1894,
                                            1896,
                                            1955,
                                            1957,
                                            2021,
                                            2020)
         AND MatchPlayerBowling.MatchType = 'Test'
         AND Fixture.MatchType = 'Test'
         INNER JOIN MatchPlayerBatting ON MatchPlayerResult.MatchID = MatchPlayerBatting.MatchID
         AND MatchPlayerResult.MatchType = MatchPlayerBatting.MatchType
         AND MatchPlayerResult.PlayerID = MatchPlayerBatting.PlayerCommonName
         AND (TeamOne in ('New Zealand')
              OR TeamTwo in ('New Zealand')
              OR TeamThree in ('New Zealand'))) T)
   GROUP BY MatchPlayerBowling.PlayerID) T1 ) Q2

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

HuaMin ChenProblem resolverCommented:
The error means you are to repeatedly use the same name/alias in more than one place.
0
PeterErhardAuthor Commented:
Yeah I realise that thanks, but can't seem to find where.
0
arnoldCommented:
Misread the error (did not look past the non unique)
You are creating an alias for a table using the table's name

Usually an alias is
Select * from mylongnamedtablename a
To simplify referencing down the line, instead of having to type mylongnamedtablename.column you will have a.column

On line 14
Your statement is
Select * from mylongnamedtablename as mylongnamedtablename
This changes the reference from mylongnamedtablename.column (original table name) to mylongnamedtablename.column (your defined alias)

An alias for table names is not a requirement.

You may have other issues, but you have to go and make sure your alias is not the same thing as the original.
0
HuaMin ChenProblem resolverCommented:
Try
SELECT (Balls / Wickets) AS StrikeRate,
          (Runs / Balls * 6) AS EconomyRate,
          PlayerID,
          Balls,
          Matches,
          Innings,
          Runs,
          Wickets,
          MaxSumBowlWickets,
          Average,
          FourWicketBags,
          FiveWicketBags
   FROM
     (SELECT PlayerID,
             SUM(IF(instr(BowlOvers, '.') != '', SUBSTR(BowlOvers, 1, instr(BowlOvers, '.') - 1), BowlOvers) * IF(Notes = '5-ball overs', 5, IF(Notes = '8-ball overs', 8, IF(Notes = '', 6, 0))) + IF(instr(BowlOvers, '.') != '', SUBSTR(BowlOvers, instr(BowlOvers, '.') + 1), 0)) AS Balls,
             COUNT(DISTINCT MatchID) AS Matches,
             COUNT(DISTINCT MatchPlayerBowlingIDAuto) AS Innings,
             SUM(BowlRuns) AS Runs,
             SUM(BowlWickets) AS Wickets,
             MAX(BowlWickets) AS MaxSumBowlWickets,
             (SUM(BowlRuns) / SUM(BowlWickets)) AS Average,
             COUNT(CASE WHEN BowlWickets = 4 THEN PlayerID end) AS FourWicketBags,
             COUNT(CASE WHEN BowlWickets >= 5 THEN PlayerID end) AS FiveWicketBags
      FROM
        (SELECT DISTINCT MatchPlayerBowling.PlayerID,
                         BowlOvers,
                         Notes,
                         MatchPlayerResult.MatchID,
                         MatchPlayerBowling.MatchPlayerBowlingIDAuto,
                         BowlRuns,
                         BowlWickets
         FROM MatchPlayerResult
         INNER JOIN Fixture ON MatchPlayerResult.MatchID = Fixture.MatchID
         INNER JOIN MatchPlayerBowling ON MatchPlayerResult.MatchID = MatchPlayerBowling.MatchID
         AND MatchPlayerResult.MatchType = MatchPlayerBowling.MatchType
         AND MatchPlayerResult.PlayerID = MatchPlayerBowling.PlayerId
         INNER JOIN Player ON MatchPlayerBowling.PlayerID = Player.PlayerId
         AND MatchPlayerBowling.MatchID in (275,
                                            728,
                                            729,
                                            730,
                                            733,
                                            735,
                                            736,
                                            796,
                                            798,
                                            887,
                                            889,
                                            891,
                                            922,
                                            924,
                                            926,
                                            1029,
                                            1030,
                                            1031,
                                            1035,
                                            1038,
                                            1040,
                                            1082,
                                            1084,
                                            1087,
                                            1129,
                                            1141,
                                            1215,
                                            1216,
                                            1217,
                                            1235,
                                            1236,
                                            1238,
                                            1383,
                                            1386,
                                            1388,
                                            1488,
                                            1491,
                                            1493,
                                            1565,
                                            1571,
                                            1573,
                                            1721,
                                            1723,
                                            1744,
                                            1742,
                                            1739,
                                            1894,
                                            1896,
                                            1955,
                                            1957,
                                            2021,
                                            2020)
         AND MatchPlayerBowling.MatchType = 'Test'
         AND Fixture.MatchType = 'Test'
         INNER JOIN MatchPlayerBatting ON MatchPlayerResult.MatchID = MatchPlayerBatting.MatchID
         AND MatchPlayerResult.MatchType = MatchPlayerBatting.MatchType
         AND MatchPlayerResult.PlayerID = MatchPlayerBatting.PlayerCommonName
         INNER JOIN MatchPlayerBatting MatchPlayerBatting2 ON MatchPlayerBowling.MatchID = MatchPlayerBatting2.MatchID
         AND MatchPlayerBowling.MatchType = MatchPlayerBatting2.MatchType
         AND MatchPlayerBowling.PlayerID = MatchPlayerBatting2.BatBowlerCommonName
         AND (TeamOne in ('New Zealand')
              OR TeamTwo in ('New Zealand')
              OR TeamThree in ('New Zealand'))) T)
   GROUP BY MatchPlayerBowling.PlayerID) T1 ) Q2

Open in new window

0
PortletPaulfreelancerCommented:
Think all has been discovered before me (that alias playerID isn't required, and each nested subquery needs its own alias) beyond that I saw nothing new - although you might look for ways to get rid of that 'distinct' - I did the reformat anyway to have a look maybe it will assist
SELECT
        (Balls / Wickets)  AS StrikeRate
      , (Runs / Balls * 6) AS EconomyRate
      , PlayerID
      , Balls
      , Matches
      , Innings
      , Runs
      , Wickets
      , MaxSumBowlWickets
      , Average
      , FourWicketBags
      , FiveWicketBags
FROM (
        SELECT
                PlayerID
              , SUM(IF(instr(BowlOvers, '.') != '', SUBSTR(BowlOvers, 1, instr(BowlOvers, '.') - 1), BowlOvers)
                  * IF(Notes = '5-ball overs', 5, IF(Notes = '8-ball overs', 8, IF(Notes = '', 6, 0)))
                  + IF(instr(BowlOvers, '.') != '', SUBSTR(BowlOvers, instr(BowlOvers, '.') + 1), 0))
                                                         AS Balls
              , COUNT(DISTINCT MatchID)                  AS Matches
              , COUNT(DISTINCT MatchPlayerBowlingIDAuto) AS Innings
              , SUM(BowlRuns)                            AS Runs
              , SUM(BowlWickets)                         AS Wickets
              , MAX(BowlWickets)                         AS MaxSumBowlWickets
              , (SUM(BowlRuns) / SUM(BowlWickets))       AS Average
              , COUNT(CASE
                        WHEN BowlWickets = 4 THEN PlayerID
                        END)                             AS FourWicketBags
              , COUNT(CASE
                        WHEN BowlWickets >= 5 THEN PlayerID
                        END)                             AS FiveWicketBags
        FROM (
        
/* distinct, can this be avoided? */

                SELECT DISTINCT
                        MatchPlayerBowling.PlayerID
                      , BowlOvers
                      , Notes
                      , MatchPlayerResult.MatchID
                      , MatchPlayerBowling.MatchPlayerBowlingIDAuto
                      , BowlRuns
                      , BowlWickets
                FROM MatchPlayerResult
                INNER JOIN Fixture
                        ON MatchPlayerResult.MatchID = Fixture.MatchID
                INNER JOIN MatchPlayerBowling
                        ON MatchPlayerResult.MatchID = MatchPlayerBowling.MatchID
                        AND MatchPlayerResult.MatchType = MatchPlayerBowling.MatchType
                        AND MatchPlayerResult.PlayerID = MatchPlayerBowling.PlayerId
                INNER JOIN Player
                        ON MatchPlayerBowling.PlayerID = Player.PlayerId
                         AND MatchPlayerBowling.MatchID IN (
                              275, 728, 729, 730, 733, 735, 736, 796, 798, 887, 889, 891, 922, 924, 926, 1029, 1030, 
                              1031, 1035, 1038, 1040, 1082, 1084, 1087, 1129, 1141, 1215, 1216, 1217, 1235, 1236, 
                              1238, 1383, 1386, 1388, 1488, 1491, 1493, 1565, 1571, 1573, 1721, 1723, 1744, 1742, 
                              1739, 1894, 1896, 1955, 1957, 2021, 2020
                              )
                        AND MatchPlayerBowling.MatchType = 'Test'
                        AND Fixture.MatchType = 'Test'
                INNER JOIN MatchPlayerBatting
                        ON MatchPlayerResult.MatchID = MatchPlayerBatting.MatchID
                        AND MatchPlayerResult.MatchType = MatchPlayerBatting.MatchType
                        AND MatchPlayerResult.PlayerID = MatchPlayerBatting.PlayerCommonName
                INNER JOIN MatchPlayerBatting
                        ON MatchPlayerBowling.MatchID = MatchPlayerBatting.MatchID
                        AND MatchPlayerBowling.MatchType = MatchPlayerBatting.MatchType
                        AND MatchPlayerBowling.PlayerID = MatchPlayerBatting.BatBowlerCommonName
                        AND (  TeamOne IN ('New Zealand')
                            OR TeamTwo IN ('New Zealand')
                            OR TeamThree IN ('New Zealand')
                            )
            ) AS INQ
    ) AS L2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterErhardAuthor Commented:
I finished up getting it working with this order of aliases.

            $sql.=") T GROUP  BY PlayerID ";
            $sql.= " ) T2";
            $sql.=" ) Q2 ";
            $sql.= "                     ON Q1.PlayerID = Q2.PlayerID ";

Thanks to all involved.

Unfortunately, I can't seem to figure out a way to get the sub-query working without the distinct and it still returns the results within 2 seconds so I'm not overly concerned.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.