Nested query and grouping by unions (SQL)

Question

I need help writing a query to get some information but I am having trouble writing it.

[table_People]
int id
var name

[table_Tools]
int id
var name

[table_Activity1]
int person_id
int tool_id
date delivery_date

[table_Activity2]
int person_id
int tool_id
date installation_date

The query needs to return a list of all people and the name of the most recent tool they used in either activity 1 or 2 (the most recent activity that happened between the two).

SELECT
    people.id   AS personId,
    people.name AS personName,
    (
        SELECT
            tools.name AS toolName
        FROM
            activity1
        JOIN
            tools ON tools.id=activity1.tool_id
        WHERE
            activity1.id=people.id
        UNION ALL
        SELECT
            tools.name AS toolName
        FROM
            activity2
        JOIN
            tools ON tools.id=activity2.tool_id
        WHERE
            activity2.id=people.id
        ORDER BY
            installationDate,deliveryDate
    ) AS toolName
FROM
    people
ORDER BY
    people.name
ASC

The problem I am having is that I can't sort by date (delivery or installation) as I get errors because they are different column names.

Answer 1

Using UNION in a subquery creates a derived temporary table. Columns that aren't selected are not in the result set, so you can't ORDER on a column that's not in the SELECT clause.

When using UNION, the first column name that is used in the SELECT clause is used in the result set (similar to an alias, though you could also use an alias).

Just be sure to name the column in the SELECT clause.

You also need a LIMIT clause to restrict the subquery to a single row:

SELECT
    people.id   AS personId,
    people.name AS personName,
    (
        SELECT
            tools.name AS toolName, delivery_date
        FROM
            activity1
        JOIN
            tools ON tools.id=activity1.tool_id
        WHERE
            activity1.id=people.id
        UNION ALL
        SELECT
            tools.name AS toolName, installation_date
        FROM
            activity2
        JOIN
            tools ON tools.id=activity2.tool_id
        WHERE
            activity2.id=people.id
        ORDER BY
            deliveryDate
        LIMIT 1
    ) AS toolName
FROM
    people
ORDER BY
    people.name
ASC

Here's a more simple example to illustrate the issue:

SELECT fish FROM sea
UNION
SELECT dog FROM land
ORDER BY fish

Is the same as:

SELECT fish AS animal FROM sea
UNION
SELECT dog AS animal FROM land
ORDER BY animal

The results are put into a derived temporary table, and you can name the columns whatever you want, but the first name that you use sticks.

Answer 2

My solution puts the unions together in a subquery and then orders by them. You only want the first row, so you need a limit clause (or rownum = 1 in Oracle or top 1 in MSSQL):

SELECT people.id   AS personId,
       people.name AS personName, 
       (SELECT toolname
        FROM ((SELECT tools.name AS toolName, delivery_date as thedate
               FROM activity1 a
               WHERE a.PersonId = people.id
              ) union all
              (SELECT tools.name AS toolName, installation_date as thedate
               FROM activity2 a
               WHERE a.PersonId = people.id
              )
             ) a join
             tools t
             on a.toolsid = t.toolsid
        order by 2 desc
        limit 1
       ) AS toolName
FROM people
ORDER BY people.name ASC

To simplify the query, I also removed the innermost join to tools.