How can i compare 2 tables in postgresql?

Question

i have a table named hotel with 2 columns named : hotel_name , hotel_price

hotel_name | hotel_price hotel1 | 5 hotel2 | 20 hotel3 | 100 hotel4 | 50

and another table named city that contains the column : city_name , average_prices

city_name | average_prices paris | 20 london | 30 rome | 75 madrid | 100

I want to find which hotel has a price that's more expensive than average prices in the cities.For example i want in the end to find something like this:

hotel_name | city_name hotel3 | paris --hotel3 is more expensive than the average price in paris hotel3 | london --hotel3 is more expensive than the average price in london etc. hotel3 | rome hotel4 | paris hotel4 | london

(I found the hotels that are more expensive than the average prices of the cities)

Any help would be valuable thank you .

Do you need this query/function often? If not I would use external code and just fetch all related DB data (if the table is very large in batches) and compare them by code not directly inside the DB. — LenglBoy
– LenglBoy, Commented Apr 30, 2021 at 5:25

Belayer · Accepted Answer · 2021-04-30 05:11:11Z

A simple join is all that is needed. Typically tables are joined on a defined relationship (PK/FK) but there is nothing requiring that. See fiddle.

select h.hotel_name, c.city_name from hotels h join cities c on h.hotel_price > c.average_prices;

However, while you can get the desired results, it's pretty meaningless. You cannot tell whether a particular hotel is even in a given city.

Collectives™ on Stack Overflow

How can i compare 2 tables in postgresql?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related