Search code examples
sqldatabasedatatables

Selecting MIN value from two tables and putting them in the same table

I have don't have a lot of experiance with SQLs. I have two tables with prices from to different comanpies. I want to select the MIN price from each company and put them in a table together.

|Company   |Start city| Stop city| Price    | 
| -------- | -------- | -------- | -------- | 
| A        | HONGKONG | OSLO     | 250      | 
| A        | BANGKOK  | OSLO     | 400      | 
| A        | BANGKOK  | OSLO     | 300      | 
| A        | HONGKOMG | OSLO     | 500      | 


|Company   |Start city| Stop city| Price    | 
| -------- | -------- | -------- | -------- | 
| B        | HONGKONG | OSLO     | 500      | 
| B        | BANGKOK  | OSLO     | 100      | 
| B        | BANGKOK  | OSLO     | 600      | 
| B        | HONGKOMG | OSLO     | 150      |

The outcome I need it if select BANGKOK - OSLO, I get the MIN value of price from each table:

|Company   |Start city| Stop city| Price    | 
| -------- | -------- | -------- | -------- | 
| A        | BANGKOK  | OSLO     | 300      | 
| B        | BANGKOK  | OSLO     | 100      |

Is this possible?

Solution

  • A good way to approach this is to work with a database view.

    This view contains all the cheapest offers collected from multiple tables

    CREATE VIEW cheapest_offers (company, start_city, stop_city, price) AS (
SELECT company,
       start_city,
       stop_city,
       MIN(price) as price
  FROM (SELECT * FROM t1 
       UNION SELECT * FROM t2) sub
 GROUP BY company, start_city, stop_city
);

    Or use the CTE suggestion that @trillion made

    You can then query this view like a normal table:

    SELECT * 
  FROM cheapest_offers
 WHERE start_city = 'BANGKOK' AND stop_city = 'OSLO'

    See https://dbfiddle.uk/MK5Q1ec8 for a working version