CODEWITHSUNDEEP
mysqlsql
modulitos
modulitos

Reputation: 15804

MySQL: Alias of Joined Tables - troubleshooting

I have a working example on SQL fiddle here: http://sqlfiddle.com/#!2/e75d6/34

with the following query:

SELECT *
FROM (Orders JOIN 
     Salesperson ON 
     Salesperson.ID = Orders.salesperson_id) 
      JOIN 
     (SELECT salesperson_id AS sid, MAX( Amount ) AS MaxOrder
      FROM Orders
      GROUP BY salesperson_id) AS TopOrderAmountsPerSalesperson
     ON (TopOrderAmountsPerSalesperson.sid=Orders.salesperson_id AND
         TopOrderAmountsPerSalesperson.MaxOrder=Orders.Amount)

but when I try to assign an alias to the first joined table, as shown here:

SELECT *
FROM (Orders JOIN 
     Salesperson ON 
     Salesperson.ID = Orders.salesperson_id) AS SalesOrders
      JOIN 
     (SELECT salesperson_id AS sid, MAX( Amount ) AS MaxOrder
      FROM Orders
      GROUP BY salesperson_id) AS TopOrderAmountsPerSalesperson
     ON (TopOrderAmountsPerSalesperson.sid=SalesOrders.salesperson_id AND
         TopOrderAmountsPerSalesperson.MaxOrder=SalesOrders.Amount)

I get a syntax error. Any ideas why?

I am using this SO as a resource for aliased joined tables: SQL Alias of joined tables

Upvotes: 1

Views: 144

Answers (1)

Sadikhasan
Sadikhasan

Reputation: 18600

You can give an alias to either the whole query OR a table name OR a field name, but not on a join condition. Try the following example with an alias to the Orders table as SalesOrders.

SELECT *
FROM Orders AS SalesOrders JOIN 
     Salesperson ON 
     Salesperson.ID = SalesOrders.salesperson_id 
      JOIN 
     (SELECT salesperson_id AS sid, MAX( Amount ) AS MaxOrder
      FROM Orders
      GROUP BY salesperson_id) AS TopOrderAmountsPerSalesperson
     ON (TopOrderAmountsPerSalesperson.sid=SalesOrders.salesperson_id AND
         TopOrderAmountsPerSalesperson.MaxOrder=SalesOrders.Amount)

Check SQL Fiddle

Upvotes: 1

Related Questions