sort table from another table with union and join

Given the following tables, I would like the final output to be sorted by department weight and also staff weight

staff

+-----+--------+-----------------+-------------+--------+
| uid | name   | position        | department  | weight |
+-----+--------+-----------------+-------------+--------+
|   1 | James  | Specialist      | Quality     |      1 |
|   2 | Robert | Specialist      | Quality     |      2 |
|   3 | Demian | Engineer        | Engineering |      3 |
|   4 | Daria  | Quality Officer | Quality     |      4 |
|   5 | Hanson | IT Officer      | ICT         |      5 |
+-----+--------+-----------------+-------------+--------+

multidepartment_staff

+----+-----+--------+------------+------------+--------+
| id | uid | name   | position   | department | weight |
+----+-----+--------+------------+------------+--------+
|  1 |   2 | Robert | Specialist | ICT        |      1 |
+----+-----+--------+------------+------------+--------+

department

+----+-------------+--------+
| id | department  | weight |
+----+-------------+--------+
|  1 | Engineering |      1 |
|  2 | ICT         |      2 |
|  3 | Quality     |      3 |
+----+-------------+--------+

A partial result

SELECT s.uid
     , s.name
     , s.position
     , s.department
     , s.weight 
  FROM staff s 
UNION ALL 
SELECT m.uid
     , m.name
     , m.position
     , m.department
     , m.weight 
  FROM multidepartment_staff m;
+-----+--------+-----------------+-------------+--------+
| uid | name   | position        | department  | weight |
+-----+--------+-----------------+-------------+--------+
|   1 | James  | Specialist      | Quality     |      1 |
|   2 | Robert | Specialist      | Quality     |      2 |
|   3 | Demian | Engineer        | Engineering |      3 |
|   4 | Daria  | Quality Officer | Quality     |      4 |
|   5 | Hanson | IT Officer      | ICT         |      5 |
|   2 | Robert | Specialist      | ICT         |      1 |
+-----+--------+-----------------+-------------+--------+
6 rows in set (0.00 sec)

im not sure if im doing this right but i tried it like this

SELECT s.uid,s.name,s.position,s.department,s.weight
FROM staff AS s
UNION ALL
SELECT m.uid,m.name,m.position,m.department,m.weight
FROM multidepartment_staff AS m JOIN department
AS d ON d.department=s.department
ORDER BY d.department ASC, s.weight ASC;

but i received error

Table ‘d’ from one of the SELECTs cannot be used in field list

Answer

Without looking too closely, perhaps you’re after something like this (although, at a glance, I don’t really see why the department table is required here – nor indeed why there are two separate tables for staff)

SELECT uid
     , x.name
     , x.position
     , d.department
     , x.weight 
  FROM department d 
  JOIN
( SELECT uid
       , name
       , position
       , weight
       , department 
  FROM staff 
  UNION ALL
   SELECT uid
       , name
       , position
       , weight
       , department 
    FROM multidepartment_staff 
) x
ON x.department = d.department
ORDER 
   BY d.department ASC
     , x.weight ASC

Advertisement

Answer