Mysql Question posted in
The official MySQL documentation can be found here.

SQL Sort by multiple columns of a different table – Mysql

I have two tables:
The first one having the columns:

|Name | Company | Job|

with values such as

(jack apple developer),
(Julian google developer),
(Jim eBay developer),
(jack apple janitor),
(Julian google janitor),
(Jim eBay janitor),
(jack apple manager),
(Julian google manager),
(Jim eBay manager)

and the second table consists of the sort order of name and job with columns

|FilterVal | Name_Sort_Order | Job_Sort_Order|** and Values


(jack,2, NULL),
(Julian, 1, NULL),
(Jim, 3, NULL),
(Janitor, NULL, 1),
(Manager, NULL, 3),
(Developer, NULL, 2)

How would I sort the first table by name_sort_order and job_sort_order of the second table?
desired result:

(Julian google janitor),
(Julian google developer),
(Julian google manager),
(jack apple janitor),
(jack apple developer),
(jack apple manager),
(Jim eBay janitor),
(Jim eBay developer),
(Jim eBay manager)
Tags:

2

Answers


  1. 0

    Hmmm . . . one method uses subqueries in the order by:

    select t1.*
from table1 t1 
order by (select t2.name_sort_order
          from table2 t2
          where t2.filterval = t.name and t2.name_sort_order is not null
         ),
         (select t2.job_sort_order
          from table2 t2
          where t2.filterval = t.job and t2.job_sort_order is not null
         );
    Login or Signup to reply.
  2. 0

    You can join second table twice (based on column ‘FilterVal’) and then order by. Crucial here is aliasing second table.

    WITH A AS(
        SELECT 'jack' Name, 'apple' Company, 'Developer' Job FROM DUAL
        UNION ALL
        SELECT 'Julian' Name, 'google' Company, 'Developer' Job FROM DUAL
        UNION ALL
        SELECT 'Jim' Name, 'eBay' Company, 'Developer' Job FROM DUAL
        UNION ALL
        SELECT 'jack' Name, 'apple' Company, 'Janitor' Job FROM DUAL
        UNION ALL
        SELECT 'Julian' Name, 'google' Company, 'Janitor' Job FROM DUAL
        UNION ALL
        SELECT 'Jim' Name, 'eBay' Company, 'Janitor' Job FROM DUAL
        UNION ALL
        SELECT 'jack' Name, 'apple' Company, 'Manager' Job FROM DUAL
        UNION ALL
        SELECT 'Julian' Name, 'google' Company, 'Manager' Job FROM DUAL
        UNION ALL
        SELECT 'Jim' Name, 'ebay' Company, 'Manager' Job FROM DUAL
        ) , B AS (
        SELECT 'jack' FilterVal, 2 NameSortOrder, NULL Job_Sort_Order FROM DUAL
        UNION ALL
        SELECT 'Julian' FilterVal, 1 NameSortOrder, NULL Job_Sort_Order FROM DUAL
        UNION ALL
        SELECT 'Jim' FilterVal, 3 NameSortOrder, NULL Job_Sort_Order FROM DUAL
        UNION ALL
        SELECT 'Janitor' FilterVal, NULL NameSortOrder, 1 Job_Sort_Order FROM DUAL
        UNION ALL
        SELECT 'Manager' FilterVal, NULL NameSortOrder, 3 Job_Sort_Order FROM DUAL
        UNION ALL
        SELECT 'Developer' FilterVal, NULL NameSortOrder, 2 Job_Sort_Order FROM DUAL
)
SELECT
        A.Name
        ,A.Company
        ,A.Job
FROM
    A LEFT OUTER JOIN B ON A.Name = B.FilterVal AND B.NameSortOrder IS NOT NULL
    LEFT OUTER JOIN B AS BB ON A.Job = BB.FilterVal AND BB.Job_Sort_Order IS NOT NULL
ORDER BY
    B.NameSortOrder, BB.Job_Sort_Order;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search