SQL Views on multiple tables

June 23, 2022

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

>Solution :

Try with inner join


CREATE VIEW IdentifyAccom AS
SELECT
     A.accom_id AS AccomID
    ,H.hotel_name AS Hotel_Name
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 Motel_Name
FROM Accommodation A
    INNER JOIN Motel M ON M.accom_id = A.accom_id