How can I set multiple aliases for a single derived table in MariaDB 5.5?

Question

Consider a database with three tables:

goods (Id is the primary key)

+----+-------+-----+
| Id | Name  | SKU |
+----+-------+-----+
|  1 | Nails | 123 |
|  2 | Nuts  | 456 |
|  3 | Bolts | 789 |
+----+-------+-----+

invoiceheader (Id is the primary key)

+----+--------------+-----------+---------+
| Id |     Date     | Warehouse | BuyerId |
+----+--------------+-----------+---------+
|  1 | '2021-10-15' |         1 |     223 |
|  2 | '2021-09-18' |         1 |     356 |
|  3 | '2021-07-13' |         2 |       1 |
+----+--------------+-----------+---------+

invoiceitems (Id is the primary key)

+----+----------+--------+-----+-------+
| Id | HeaderId | GoodId | Qty | Price |
+----+----------+--------+-----+-------+
|  1 |        1 |      1 |  15 |   1.1 |
|  2 |        1 |      3 |   7 |   1.5 |
|  3 |        2 |      1 |  12 |   1.5 |
|  4 |        3 |      3 |   3 |   1.3 |
+----+----------+--------+-----+-------+

What I'm trying to do is to get the MAX(invoiceheader.Date) for every invoiceitems.GoodId. Or, in everyday terms, to find out, preferably in a single query, when was the last time any of the goods were sold, from a specific warehouse.

To do that, I'm using a derived query, and the solution proposed here . In order to be able to do that, I think that I need to have a way of giving multiple (well, two) aliases for a derived table.

My query looks like this at the moment:

SELECT tmp.*   /* placing the second alias here, before or after tmp.* doesn't work */
FROM (         /* placing the second alias, tmpClone, here also doesn't work */
    SELECT
        invoiceheader.Id,
        invoiceheader.Date,
        invoiceitems.HeaderId,
        invoiceitems.Id,
        invoiceitems.GoodId
    FROM invoiceheader
    LEFT JOIN invoiceitems
        ON invoiceheader.Id = invoiceitems.HeaderId
    WHERE invoiceheader.Warehouse = 3
    AND invoiceheader.Date > '0000-00-00 00:00:00'
    AND invoiceheader.Date IS NOT NULL
    AND invoiceheader.Date > ''
    AND invoiceitems.GoodId > 0
    ORDER BY 
        invoiceitems.GoodId ASC,
        invoiceheader.Date DESC
) tmp, tmpClone /* this doesn't work with or without a comma */
INNER JOIN (
    SELECT
        invoiceheader.Id,
        MAX(invoiceheader.Date) AS maxDate
    FROM tmpClone
    WHERE invoiceheader.Warehouse = 3
    GROUP BY invoiceitems.GoodId
) headerGroup
    ON tmp.Id = headerGroup.Id
    AND tmp.Date = headerGroup.maxDate
    AND tmp.HeaderId = headerGroup.Id

Is it possible to set multiple aliases for a single derived table? If it is, how should I do it?

I'm using 5.5.52-MariaDB.

Depending on your version of mysql, I believe a [common table expression (CTE)](https://dev.mysql.com/doc/refman/8.0/en/with.html) could be your best option. CTEs allow you to logically build on top of your former query results instead of using multiple subqueries. MySQL 8.0+ supports CTEs. — Kurt Kline, Oct 15 '21 at 15:45
It is unclear what you really want to do, because you only explain that 'something' you tried with aliases does not work, but you fail to explain the expected output, and failed to explain why (you think you need) a second alias. — Luuk, Oct 15 '21 at 17:15
@Luuk I actually mentioned that in the question: What I'm trying to do is to get the MAX(invoiceheader.Date) for every invoiceitems.GoodId. Or, in everyday terms, to find out, preferably in a single query, when was the last time any of the goods were sold, from a specific warehouse. — FiddlingAway, Oct 15 '21 at 17:29
But i do not see a `MAX()` in your query, and a long story about aliases. I fail to know what the relation between MAX and the (multiple) use of aliases is. — Luuk, Oct 15 '21 at 17:31
@Luuk `MAX()` is actually in the `INNER JOIN` part of the query - the idea is to join the table with itself by the corresponding `MAX ` date id, because aggregating + grouping + joining does not return the correct data. — FiddlingAway, Oct 15 '21 at 17:54
Us can use the CTE, but since you are on MariaDB5.5, the only way might be to create a temporary table with the values from your subquery `tmp`. then you can use this temporary table as often as you need. — Luuk, Oct 15 '21 at 18:26

score 0 · Answer 1 · answered Oct 15 '21 at 15:40

0

you can use both (inner select) and left join to achieve this for example: select t1.b,(select t2.b from table2 as t2 where t1.x=t2.x) as 'Y' from table as t1 Where t1.y=(select t3.y from table3 as t3 where t2.a=t3.a)

answered Oct 15 '21 at 15:40

Irakli Shalamberidze

302
3
11

score 0 · Answer 2 · answered Oct 16 '21 at 13:22

While this doesn't answer my original question, it does solve the problem from which the question arose, and I'll leave it here in case anyone ever comes across a similar issue.

The following query does what I'd intended to do - find the newest sale date for the goods from the specific warehouse.

SELECT
    invoiceheader.Id,
    invoiceheader.Date,
    invoiceitems.HeaderId,
    invoiceitems.Id,
    invoiceitems.GoodId
FROM invoiceheader
INNER JOIN invoiceitems
    ON invoiceheader.Id = invoiceitems.HeaderId
INNER JOIN (
    SELECT
        MAX(invoiceheader.Date) AS maxDate,
        invoiceitems.GoodId
    FROM invoiceheader
    INNER JOIN invoiceitems
        ON invoiceheader.Id = invoiceitems.HeaderId
    WHERE invoiceheader.Warehouse = 3
    AND invoiceheader.Date > '0000-00-00 00:00:00'
    AND invoiceheader.Date IS NOT NULL
    AND invoiceheader.Date > ''
    GROUP BY invoiceitems.GoodId
) tmpDate
    ON invoiceheader.Date = tmpDate.maxDate
    AND invoiceitems.GoodId = tmpDate.GoodId
WHERE invoiceheader.Warehouse = 3
AND invoiceitems.GoodId > 0
ORDER BY 
    invoiceitems.GoodId ASC,
    invoiceheader.Date DESC

The trick was to join by taking into consideration two things - MAX(invoiceheader.Date) and invoiceitems.GoodId - since one GoodId can only appear once inside a specific invoiceheader / invoiceitems JOINing (strict limit imposed on the part of the code which inserts into invoiceitems).

Whether this is the most optimal solution (ignoring the redundant conditions in the query), and whether it would scale well, remains to be seen - it has been tested on tables with ~5000 entries for invoiceheader, ~60000 entries for invoiceitems, and ~4000 entries for goods. Execution time was < 1 sec.

How can I set multiple aliases for a single derived table in MariaDB 5.5?

2 Answers2