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

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?

CodePudding user response：

first combine two tables using the union all - make sure you have the same number of columns and in the same order just like you are showing in the example
then for a given company, for a given start and stop city you are finding the minimum value of price

with main as (

select * from table1
union all
select * from table2
)

select 
company,
start_city,
stop_city,
min(price) as minimum_price
from main
group by 1,2,3

CodePudding user response：

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