How to order by using column values in reference table in mysql?

Question

I have query like this,

Select f.* from fuel_log f 
where f.transaction_date='2006-05-28 00:00:00' 
order by f.driver.full_name;

My problem is, i am not able to order by using f.driver.full_name. It says no such column. However, that's correct because, driver is reference column in fuel_log table. Here i am trying to sort based on values in driver table which is causing a problem. May i know how can in sort based on values in reference table column?. Thanks in advance.

score 0 · Answer 1 · answered May 02 '14 at 03:59

0

As you said driver is referenced column which is common between two tables then you can join both tables to sort it by reference column.

Please modify order by clause as per you need full_name or driver column.

Select   f.* 
from     fuel_log f inner join driver d
on       f.driver = d.driver
where    f.transaction_date='2006-05-28 00:00:00' 
order by d.driver;

answered May 02 '14 at 03:59

Vishwanath Dalvi

35,388
41
123
155

Thanks for the answer. Anyway, i am already joining table that is fuel_log and fuel_inspection. It is only for simplicity and for better understanding i posted normal query. – Kishan_KP May 02 '14 at 04:02
will it allow to join third table also? – Kishan_KP May 02 '14 at 04:02
If you're able to join then there shouldn't be any problem while sorting. – Vishwanath Dalvi May 02 '14 at 04:02
@Kishan_KP yes for your reference http://stackoverflow.com/questions/3709560/mysql-join-three-tables – Vishwanath Dalvi May 02 '14 at 04:03
I am not joining fuel_log and driver table. I am joining fuel_log and fuel_inspection. however, driver is reference column in both of this table. – Kishan_KP May 02 '14 at 04:04

How to order by using column values in reference table in mysql?

1 Answers1