Home > Enterprise >  SQL subqueries"Every derived table must have its own alias" error
SQL subqueries"Every derived table must have its own alias" error

Time:06-10

I have this query with multiple subqueries and I got an error message "Every derived table must have its own alias".

SELECT SUM(set1   set2   set3)
FROM (
SELECT COUNT(b.`Set1-p1`) as set1 FROM synbadminton as b
INNER JOIN syngame as g 
ON b.GameID = g.GameID
WHERE b.`Set1-p1` > b.`Set1-p2` && g.PlayerOneID = 12   (
    
SELECT COUNT(b.`Set2-p1`) as set2 FROM synbadminton as b
INNER JOIN syngame as g 
ON b.GameID = g.GameID
WHERE b.`Set2-p1` > b.`Set2-p2` && g.PlayerOneID = 12   (

SELECT COUNT(b.`Set3-p1`) as set3 FROM synbadminton as b
INNER JOIN syngame as g 
ON b.GameID = g.GameID
WHERE b.`Set3-p1` > b.`Set3-p2` && g.PlayerOneID = 12 ) 
    )
) 

I tried to name the tables at the end but then I got this error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS T1) AS T2 ) AS T3 ) AS T4 LIMIT 0, 50' at line 16"

SELECT SUM(set1   set2   set3)
FROM (
SELECT COUNT(b.`Set1-p1`) as set1 FROM synbadminton as b
INNER JOIN syngame as g 
ON b.GameID = g.GameID
WHERE b.`Set1-p1` > b.`Set1-p2` && g.PlayerOneID = 12   (
    
SELECT COUNT(b.`Set2-p1`) as set2 FROM synbadminton as b
INNER JOIN syngame as g 
ON b.GameID = g.GameID
WHERE b.`Set2-p1` > b.`Set2-p2` && g.PlayerOneID = 12   (

SELECT COUNT(b.`Set3-p1`) as set3 FROM synbadminton as b
INNER JOIN syngame as g 
ON b.GameID = g.GameID
WHERE b.`Set3-p1` > b.`Set3-p2` && g.PlayerOneID = 12 AS `T1`) AS `T2`
) AS `T3`
) AS `T4`

CodePudding user response:

If you want to run three queries, each returning a single numeric result, and then add them up, yielding a single number:

SELECT 
    (
    SELECT COUNT(b.`Set1-p1`) as set1 
    FROM synbadminton as b
    INNER JOIN syngame as g 
    ON b.GameID = g.GameID
    WHERE b.`Set1-p1` > b.`Set1-p2` 
          AND g.PlayerOneID = 12
    )
      (
    SELECT COUNT(b.`Set2-p1`) as set2 
    FROM synbadminton as b
    INNER JOIN syngame as g 
    ON b.GameID = g.GameID
    WHERE b.`Set2-p1` > b.`Set2-p2` 
          AND g.PlayerOneID = 12
    )
      (
    SELECT COUNT(b.`Set3-p1`) as set3 
    FROM synbadminton as b
    INNER JOIN syngame as g 
    ON b.GameID = g.GameID
    WHERE b.`Set3-p1` > b.`Set3-p2` 
          AND g.PlayerOneID = 12 
    ) 

  • Related