CODEWITHSUNDEEP
mysqlsql
daveliu2
daveliu2

Reputation: 39

Giving a derived table an alias produces an error

If I run the following code I get 'every derived table must have its own alias' but if I try to include an alias at the end of GROUP BY itemID) with AS Cust or something, I get a syntax error. What's going on here?

SELECT * FROM(
(SELECT itemID, COUNT(itemID)
FROM Item
GROUP BY itemID) AS Head

LEFT OUTER JOIN(

SELECT * FROM(
SELECT itemID
FROM (Customer LEFT OUTER JOIN Bid ON custNum = buyerNum) LEFT OUTER JOIN Item USING (itemID)
WHERE custNum = 'pxtfj044'
GROUP BY itemID)) AS Now

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You have too many parentheses and your LEFT JOIN needs an ON clause:

SELECT *
FROM (SELECT itemID, COUNT(itemID)
      FROM Item
      GROUP BY itemID
     ) Head LEFT OUTER JOIN
     (SELECT itemID
      FROM Customer LEFT OUTER JOIN
           Bid
           ON custNum = buyerNum LEFT OUTER JOIN
           Item
           USING (itemID)
      WHERE custNum = 'pxtfj044'
      GROUP BY itemID
     ) Now
     ON . . .

Upvotes: 1

Related Questions