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
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