CODEWITHSUNDEEP
mysqlsorting
Nita
Nita

Reputation: 561

mysql - sorting data from 2 tables by the value from the 3rd table

I would like to sort data from 2 different tables connected with UNION, sorting parameter has to come from from the 3rd table.(drivers > queno)

If I sort data from 1 table i use following code (works perfectly):

SELECT quotedb.*
FROM   quotedb
       LEFT JOIN drivers
              ON quotedb.driver = drivers.id
ORDER  BY IF(queno = ''
              OR queno IS NULL, 1, 0)

So when I join 2 tables I tried with this with no succes...

  (
 SELECT DISTINCT driver
 FROM            quotedb
 LEFT JOIN       drivers
 ON              quotedb.driver=drivers.id)
UNION ALL
          (
                          SELECT DISTINCT driver
                          FROM            packaging
                          LEFT JOIN       drivers
                          ON              packaging.driver=drivers.id )
ORDER BY
order by  IF(queno = ''
OR        queno IS NULL,1,0)

What i need to do to make it work?. Thank you in advance.

Upvotes: 1

Views: 22

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You will need to fetch the queno column also from individual Select queries.

Try the following:

(
 SELECT DISTINCT 
   qdb.driver AS driver, 
   d.queno AS queno 
 FROM quotedb AS qdb 
 LEFT JOIN drivers AS d ON qdb.driver = d.id
)
UNION ALL
(
 SELECT DISTINCT 
   p.driver AS driver, 
   d.queno AS queno 
 FROM packaging AS p 
 LEFT JOIN drivers AS d ON p.driver = d.id 
)
ORDER BY 
  (CASE WHEN queno = '' OR queno IS NULL THEN 1 
   ELSE 0
   END)

Upvotes: 1

Related Questions