0

I have the issue of getting the records out of database in the specific condintion. I have table 'test' I want to get the listing from sorted by driverid and table 'drivers' which i use to adjust sorting of the listing from 'test' table.

My query:

SELECT * FROM test JOIN drivers ON test.driverid=drivers.driverid ORDER BY queno

Table 'drivers' looks like:

driver | driverid | queno
-------------------
drv1   | 15       | 3
drv2   | 30       | 1
drv3   | 40       | 2

Problem is when there is no value assigned to 'driverid' in 'test' table then these results are listed at very beginning. I would like to have these listed at the end

How to achieve that? Thx in advance!

Nita
  • 561
  • 5
  • 28
  • 2
    Use full outer join. https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – Bananas Apr 04 '18 at 12:43
  • **I'm sorry i had some testing issue and had to adjust essence of the question** – Nita Apr 04 '18 at 12:51
  • **when there is no value assigned to 'driverid' in 'test' table then these results are listed at very beginning**. You are sure? If `test` is not assigned to a `driver`, the driver should be not in the results. – terry.qiao Apr 04 '18 at 13:03

3 Answers3

0

You can make driver.driverid primary key (PK) and test.driverid foreign key (FK) and enforce data integrity. This will also eliminate your problem.

Radu
  • 1,159
  • 3
  • 22
  • 40
0

Place a minus sign (-) before the column name and switch the ASC to DESC or DESC to ASC order (opposite to what you want).

try this:-

SELECT * FROM test JOIN drivers ON test.driverid=drivers.driverid ORDER BY -queno DESC;

Note:- While this may work well for numbers and dates, it may not be the best solution to sort fields with alpha or alphanumeric values

gajju_15
  • 527
  • 4
  • 17
0

I found the working solution elswhere:

MySQL: Order by field, placing empty cells at end

SELECT * FROM test JOIN drivers ON test.driverid=drivers.driverid ORDER BY if(queno = '' or queno is null,1,0), queno
Nita
  • 561
  • 5
  • 28