SQL Select and Sort from Multiple tables-CodePudding

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?

CodePudding user response：

Actually, another person had an answer and deleted it which is what I would have also offered. The only difference is that SQL-Server allows the "TOP" keyword and MySQL uses "LIMIT".

By doing each table individually, you would order by the arrival date in DESCENDING order, thus floating the most recent entries to the top of the list. THEN, applying the LIMIT 5 will limit the results to only returning the first 5 AFTER the sorted results.

By doing a UNION of each table source (clothes, toys, tools), the concept is the same. I just tacked on a free-form description, so you know from which table it originated.

The UNION just requires that each table result has the same columns and number of columns. In this case, they are all the same and same name too.

select * from
   ( select
          'Clothes' as TableSource,
          id,
          productname,
          manufacturer,
          arrivaldate
       from
          Clothes
       order by
          arrivaldate desc
       limit 5 ) QryClothes    
UNION
select * from
   ( select
          'Toys' as TableSource,
          id,
          productname,
          manufacturer,
          arrivaldate
       from
          Toys
       order by
          arrivaldate desc
       limit 5 ) QryToys
UNION
select * from
   ( select
          'Tools' as TableSource,
          id,
          productname,
          manufacturer,
          arrivaldate
       from
          Tools
       order by
          arrivaldate desc
       limit 5 ) QryTools

CodePudding user response：

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;