CREATE PROCEDURE [dbo].[GetToursList](
@toCity nvarchar(30) = null
, @toCountry nvarchar(30) = null
, @departureDate date
, @amountNights int
, @amountAdults int)
AS
BEGIN
--DECLARE @toCityID int
--DECLARE @toCuntry int
--SET @toCityID = (SELECT Cities.Id FROM dbo.Cities WHERE Cities.Name = @toCity)
--SET @toCuntry = (SELECT Countrys.Id from dbo.Countrys where Countrys.Name = @toCuntry)
SELECT
hotels.Name as HotelName
,cities.Name as CityName
,countrys.Name as CounrtyName
,Tours.Price
,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 CountryID = countrys.Id
INNER JOIN dbo.Cities as cities
ON CityID = cities.Id
INNER JOIN dbo.Hotels as hotels
ON HotelID = hotels.Id
WHERE
(@toCountry is null or(CountryID = (select Countrys.Id from dbo.Countrys where Countrys.Name = @toCountry)))--подчеркивает CountryID
and
(@toCity is null or(CityID = (select Cities.Id from dbo.Cities where Cities.Name = @toCity)))
END
Procedure: [dbo].[GetToursList] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[Cities].[CountryID], [dbo].[Countrys].[CountryID], [dbo].[Hotels].[CountryID] or [dbo].[Tours].[CountryID]. dbo.GetToursListByCity
(@toCountry is null or(CountryID = (select countrys.Id from countrys where countrys.Name = @toCountry)))
INNER JOIN dbo.Cities as cities
ON CityID = cities.Id
CREATE TABLE [dbo].[Tours] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[HotelID] INT NOT NULL,
[FromDate] DATE NOT NULL,
[ToDate] DATE NOT NULL,
[Price] INT NOT NULL,
[CityID] INT NOT NULL,
[CountryID] INT NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[Cities] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (90) NOT NULL,
[CountryID] INT NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[Countrys] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (90) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[Hotels] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[City] INT NOT NULL,
[Rating] INT NOT NULL,
[Name] NVARCHAR (60) NOT NULL,
[DistanceToBeach] INT NULL,
[Line] INT NULL,
[DistanceToAirport] INT NOT NULL,
[Discription] NVARCHAR (MAX) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
dbo.
Перед именем поля всегда указывайте из какой таблицы.CountryID
не определено из какой таблицы: уже указанной [Tours]
или из связанной [Cities]
.[Tours]
[HotelID] INT NOT NULL
[CityID] INT NOT NULL,
[CountryID] INT NOT NULL,
Гостиница может быть без города, город — без страны? Нет. Ошибка.