How to select the minimum value from a table in a query?

Question

Transact SQL

There is a procedure:

CREATE PROCEDURE [dbo].[GetToursList](	
   @toCity nvarchar(30) 
   , @toCountry nvarchar(30) 
   ,  @departureDate date
   , @amountNights int)
AS
BEGIN
SELECT 
hotels.Name as HotelName
,cities.Name as CityName
,countrys.Name as CounrtyName
,(select min((select hotelsRooms.price from hotelsRooms where hotelsRooms.HotelID = hotels.Id))) as MinPrice// здесь мучаюсь
,hotels.Rating as Stars
,hotels.Line as Line
,hotels.DistanceToAirport as DistToAirport
,hotels.DistanceToBeach as DistTobeach
FROM dbo.Tours 
INNER JOIN dbo.Countrys as countrys
ON Tours.CountryID = countrys.Id
INNER JOIN dbo.Cities as cities
ON Tours.CityID = cities.Id
INNER JOIN dbo.Hotels as hotels 
ON Tours.HotelID = hotels.Id
INNER JOIN dbo.HotelsRooms as hotelsRooms
ON hotelsRooms.HotelID = hotels.Id
WHERE
(@toCity is null or(Tours.CityID =       (select cities.Id   from cities   where   cities.Name = @toCity)))
or 
(@toCountry is null or(Tours.CountryID = (select countrys.Id from countrys where countrys.Name = @toCountry)))
END

In hotelRooms, each hotel has its own rooms with its own data.

for example

id hotelID roomTypeID Price Count
431 14 1 4500 10
432 14 2 5625 10
433 14 3 5400 10
434 14 4 6165 10
435 14 5 6525 10
436 15 1 5600 10
437 15 2 7000 10
438 15 3 6720 10
439 15 4 7672 10
440 15 5 8120 10
441 16 1 5700 10
442 16 2 7125 10
443 16 3 6840 10
444 16 4 7809 10

In the MinPrice field, the procedure should return the minimum price for the number. For example, for a hotel with ID 14 - this will be 4500.
The procedure is not updated -

(69,1): SQL72014: .Net SqlClient Data Provider: Msg 130, Level 15, State 1, Procedure GetToursList, Line 12 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
(58.0): SQL72045: Script execution error. The executed script:

line 12 - this is the very place where I suffer.
Or in one request such things are not written? Can expediency be divided into different procedures?

Answer 1

(SELECT MIN( hotelsRooms.price) FROM hotelsRooms WHERE hotelsRooms.HotelID = hotels.Id ) as MinPrice

Structure

В вопросе

[Tours]
    [HotelID]         INT            NOT NULL
    [CityID]          INT            NOT NULL,
    [CountryID]       INT            NOT NULL,

Гостиница может быть без города, город — без страны? Нет. Нужно удалить поля город и страна, а использовать связанные таблицы.
Предлагаю вариант запроса с учётом исправления структуры:

SELECT Hotels.[Name] AS HotelName, 
       Cities.[Name] AS CityName, 
       Countrys.[Name] AS CounrtyName,
       (SELECT MIN(Price)
          FROM HotelsRooms
          WHERE (HotelID = Hotels.ID)) AS MinPrice, 
        Hotels.Rating AS Stars, 
        Hotels.Line, 
        Hotels.DistanceToBeach, 
        Hotels.DistanceToAirport
  FROM Hotels 
    INNER JOIN Cities ON Hotels.City = Cities.ID 
    INNER JOIN Countrys ON Cities.CountryID = Countrys.ID
  WHERE ((@toCity IS NULL) OR (Cities.[Name] = @toCity)) 
    AND ((@toCountry IS NULL) OR (Countrys.[Name] = @toCountry))

Установите SSMS и используйте конструктор запросов. Например, сделать подзапрос

SELECT MIN(Price) FROM HotelsRooms WHERE (HotelID = 14)

Потом перенести его в

SELECT        (SELECT        MIN(Price) AS Expr1
                          FROM            HotelsRooms
                          WHERE        (HotelID = 14)) AS Expr1
FROM            Hotels
WHERE        (Id = 14)

Связать таблицы

SELECT        (SELECT        MIN(Price) AS Expr1
                          FROM            HotelsRooms
                          WHERE        (HotelID = Hotels.Id)) AS MinPrice
FROM            Hotels
WHERE        (Id = 14)