CODEWITHSUNDEEP
mysqlsql
BenM
BenM

Reputation: 53198

MySQL "Every derived table must have its own alias"

I am a little confused by this strange MySQL behaviour. I am receiving the dreaded Every derived table must have its own alias error.

Usually, I can circumvent this by adding an AS clause to the primary SELECT statement, however it does not see to work with the following example:

SELECT SUM (`a`.`total`) AS `total` FROM (
    SELECT COUNT(DISTINCT(`item_sales`.`id`)) AS `total`,
    (SELECT COUNT(DISTINCT(`sale_item`)) AS `offers` FROM `item_sales_bids` WHERE `user_id` = 2) AS `offers`
        FROM `item_sales`
        INNER JOIN `item_sales_bids` ON `item_sales`.`id` = `item_sales_bids`.`sale_item`
        WHERE `item_sales`.`buyer` != 2
            AND `item_sales`.`sold` = 1
        GROUP BY `item_sales`.`id`
        HAVING `offers` > 0
    ) UNION (
        SELECT COUNT(*) AS `total`,
            `item_sales`.`reserve`,
            (SELECT COUNT(*) FROM `item_sales_bids` WHERE `user_id` = 2) AS `bids`,
            (SELECT MAX(`max_bid`) AS `max` FROM `item_sales_bids` WHERE `user_id` = 2) AS `maxBid`,
            SUM((`item_sales`.`list_date` + (`item_sales`.duration * 86400)) - UNIX_TIMESTAMP()) AS `endTime`
            FROM `item_sales`
            INNER JOIN `item_sales_bids` ON `item_sales_bids`.`sale_item` = `item_sales`.`id`
            GROUP BY `item_sales`.`id`
            HAVING `endTime` < 0
                AND `maxBid` < `item_sales`.`reserve`
    )
) `a`

Can anyone point out what I am missing? Please note that adding an alias to the joins results in the same error.

Upvotes: 5

Views: 2910

Answers (2)

Robert
Robert

Reputation: 25753

You should put an UNION keyword inside the subquery as below

SELECT SUM (`a`.`total`) AS `total` 
FROM (
    SELECT COUNT(DISTINCT(`item_sales`.`id`)) AS `total`,
    (SELECT COUNT(DISTINCT(`sale_item`)) AS `offers` FROM `item_sales_bids` WHERE `user_id` = 2) AS `offers`
    FROM `item_sales`
    INNER JOIN `item_sales_bids` ON `item_sales`.`id` = `item_sales_bids`.`sale_item`
    WHERE `item_sales`.`buyer` != 2
        AND `item_sales`.`sold` = 1
    GROUP BY `item_sales`.`id`
    HAVING `offers` > 0

UNION 

SELECT COUNT(*) AS `total`,
    `item_sales`.`reserve`,
    (SELECT COUNT(*) FROM `item_sales_bids` WHERE `user_id` = 2) AS `bids`,
    (SELECT MAX(`max_bid`) AS `max` FROM `item_sales_bids` WHERE `user_id` = 2) AS `maxBid`,
    SUM((`item_sales`.`list_date` + (`item_sales`.duration * 86400)) - UNIX_TIMESTAMP()) AS `endTime`
    FROM `item_sales`
    INNER JOIN `item_sales_bids` ON `item_sales_bids`.`sale_item` = `item_sales`.`id`
    GROUP BY `item_sales`.`id`
    HAVING `endTime` < 0
        AND `maxBid` < `item_sales`.`reserve`
) `a`

Upvotes: 4

John Woo
John Woo

Reputation: 263723

you can include UNION inside the subquery,

SELECT SUM (`a`.`total`) AS `total`
FROM
(
    --- your first query
    UNION
    --- your second query
) a

Upvotes: 1

Related Questions