SQL Select and Sort from Multiple tables

Question

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?

Answer 1

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;