Column 'thetimestamp' in order clause is ambiguous

craptakular · 18 Aug 2012 at 12:04

I have a mysql database and I have successfully joined four tables, two of which have the same column titled 'thetimestamp'.

I have written a script to query these and join, then order by thetimestamp but it is erroring with:

Column 'thetimestamp' in order clause is ambiguous

how can i tell it that table1.thetimestamp = table2.thetimestamp = table3.thetimstamp etc etc

?

HungryHippos · 18 Aug 2012 at 12:54

This is for SQL, but I understand the syntax is similar in most cases. Joins Customers to Orders and then orders by the order timestamp value.

SELECT
c.*, o.*
FROM Customers AS c
INNER JOIN Orders AS o
ON o.CustomerID = c.CustomerID
ORDER BY o.TimeStamp

orderoftheflame · 18 Aug 2012 at 13:05

Post the whole query?

craptakular · 18 Aug 2012 at 14:15

PHP:

          $query =		("SELECT * 
						FROM parentcompany
						JOIN products
						ON parentcompany.idoperators = products.idoperator JOIN company1
						ON products.idProducts = company1.idproducts JOIN company2
						ON products.idProducts = company2.idproducts

						ORDER BY thetimestamp DESC
                        LIMIT 3");

sadbuttrue · 18 Aug 2012 at 14:56

You need to place the table name in the order clause, since you have the same column name in multiple tables.

order by table1.thetimestamp

If the timestamp is the same in all tables it doesn't matter which you choose to order by.

craptakular · 18 Aug 2012 at 15:48

Well it's the same column name but obviously different Stamp times. I'll try it when I got home thanks

HungryHippos · 18 Aug 2012 at 18:23

yes the timestamps will be different but that is irrelevant, you can only order by the timestamp field on one of the tables, the rest will just be in whatever order.

You can do like this to order by multiple columns though:

ORDER BY table1.thetimestamp, table2.thetimestamp

It will then order by the table1 timestamp field, then by table2 timestamp.