MySQL: Alias of Joined Tables - troubleshooting

Question

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

score 1 · Accepted Answer · edited Jun 28 '14 at 08:36

1

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

edited Jun 28 '14 at 08:36

modulitos

14,737
16
67
110

answered Jun 28 '14 at 08:10

Sadikhasan

18,365
21
80
122

I cant understand what you say. please explain again – Sadikhasan Jun 28 '14 at 08:24
You are a life saver! I am still confused. Does my original join, `Orders JOIN Salesperson ON Salesperson.ID = Orders.salesperson_id`, return a table? If it returns a table, why can't I assign an alias to this table? From my original question, I do not understand why `(Orders JOIN Salesperson ON Salesperson.ID = Orders.salesperson_id) AS SalesOrders` does not work. – modulitos Jun 28 '14 at 08:31
1

`(Orders JOIN Salesperson ON Salesperson.ID = Orders.salesperson_id) AS SalesOrders` This is join condition and that does not return table – Sadikhasan Jun 28 '14 at 08:33

MySQL: Alias of Joined Tables - troubleshooting

1 Answers1