SQL Views on multiple tables

Question

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

It's *2022*, it's *long* past time you adopted the ANSI-92 explicit JOIN syntax by now; it has been around for **30 years**. [Bad Habits to Kick : Using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) — Thom A, Jun 23 '22 at 15:11
I'm not really sure what you are asking here, if I am honest; how does a `VIEW` identify what accommodation type a property is? — Thom A, Jun 23 '22 at 15:12
A View doesn't identify this, the WHERE clause does this for us. Is there a way to include multiple WHERE clauses here between different tables? — Hamza, Jun 23 '22 at 15:18
As a veteran of the hospitality industry, why Hotel name and Motel name. Why not INN or SPA or RESORT or LODGE or B&B or HOSTEL ? A name is a name. — John Cappelletti, Jun 23 '22 at 15:33
Arguably you should have a `Type` column which identifies what type it is (and the primary key and foreign key is across both this and the ID). In your current schema, theoretically it could be multiple types without that. See https://stackoverflow.com/a/9449031/14868997 and https://dba.stackexchange.com/a/294823/220697 — Charlieface, Jun 23 '22 at 16:17

czeskj · Answer 1 · 2022-06-23T16:04:35.820

2

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

edited Jun 23 '22 at 16:04

answered Jun 23 '22 at 15:13

czeskj

33
5

Presumably this is going to return zero rows? How can an accommodation be made at both a `Hotel` and `Motel`? – Derrick Moeller Jun 23 '22 at 15:33
@DerrickMoeller That's true, using union solve the problem – czeskj Jun 23 '22 at 16:03
1

How quick OP is to mark an answer when it does not solve the actual question of "so that I can identify what a particular accommodation ID refers to". And it is likely that UNION ALL is preferred here because there is no need to remove duplicates. – SMor Jun 23 '22 at 18:04

score 2 · Accepted Answer · answered Jun 23 '22 at 15:42

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

SQL Views on multiple tables

2 Answers2