SQL Views on multiple tables-CodePudding

I have a database of a Travel Agency which contains tables Hotel, Motel and RentalHome, all of which refer to the Accommodation table using a foreign key that references the accommodation_id of that table.

I wish to create a View so that I can identify what a particular accommodation ID refers to; Hotel, Motel, or RentalHome.

CREATE VIEW IdentifyAccom AS
SELECT Accommodation.accom_id AS AccomID, Hotel.hotel_name AS Hotel_Name
FROM Accommodation, Hotel
WHERE Accommodation.accom_id = Hotel.accom_id

How can I go about this? I tried the following approach but to no avail.

CREATE VIEW IdentifyAccom AS
SELECT Accommodation.accom_id AS AccomID, Hotel.hotel_name AS Hotel_Name, Motel.motel_name 
FROM Accommodation, Hotel, Motel
WHERE Accommodation.accom_id = Hotel.accom_id,
WHERE Accommodation.accom_id = Motel.accom_id

CodePudding user response：

Presumably all accomodations are either a Hotel or a Motel? If so you can use something like IIF to determine the type and ISNULL or COALESCE to populate a name.

CREATE VIEW IdentifyAccom
AS
SELECT a.accom_id AS AccomID, IIF(h.accom_id IS NOT NULL, 'HOTEL', 'MOTEL') AS Accom_Type,
       ISNULL(h.hotel_name, m.motel_name) AS Accom_Name
FROM Accommodation a
LEFT JOIN Hotel h ON h.accom_id = a.accom_id
LEFT JOIN Motel m ON m.accom_id = a.accom_id

CodePudding user response：

Try with inner join


CREATE VIEW IdentifyAccom AS
SELECT
     A.accom_id AS AccomID
    ,H.hotel_name AS Accom
FROM Accommodation A
    INNER JOIN Hotel H ON H.accom_id = A.accom_id

UNION

SELECT
     A.accom_id AS AccomID
    ,M.motel_name AS Accom
FROM Accommodation A
    INNER JOIN Motel M ON M.accom_id = A.accom_id