I have three tables:
Table Clothes
|id | productname | manufacturer | arrivaldate |
+----+-------------+-----------------+--------------+
| 1 | Shirt | MfrA | 5/30/20 |
| 2 | Tshirt | MfrB | 3/17/20 |
Table Toys
|id | productname | manufacturer | arrivaldate |
+----+-------------+------------------+--------------+
| 1 | Car | MfrC | 2/16/19 |
| 2 | Ball | MfrD | 8/25/20 |
Table Tools
|id | productname | manufacturer | arrivaldate |
+----+-------------+------------------+--------------+
| 1 | Hammer | MfrE | 4/17/20 |
| 2 | Drill | MfrF | 9/12/21 |
I would like to display 5 latest/newest products, included from each table, sorted by arrivaldate DESC. It can be a new DB View or Table.
Desired output would be like this:
Latest products arrival
| productname | manufacturer | arrivaldate |
+-------------+------------------+--------------+
| Drill | MfrF | 9/12/21 |
| Ball | MfrD | 8/25/20 |
| Shirt | MfrA | 5/30/20 |
What would be SQL query for this?
If by display 5 latest/newest products, included from each table
you mean 5 latest from the combined result set, a view using a UNION will do the job:
create view testvw as select * from
(
(select productname,manufacturer,arrivaldate from Clothes)
UNION
(select productname,manufacturer,arrivaldate from Toys)
UNION
(select productname,manufacturer,arrivaldate from Tools)
) x
order by arrivaldate desc limit 5;