Simple SQL query

Question

Post reply

Simple SQL query

johnhobbs

SSC Enthusiast

Points: 112
More actions
April 24, 2021 at 3:37 pm

#3875251
MOTEL (motelID, motelName, town)
ROOM (roomID, motelNo, type, cost)
BOOKING (motelNo, guestID, dateFrom, dateTo, roomID)
GUEST (guestID, guestName, guestAddress)

For the above SQL database relation, I have two questions that I'm trying to answer:
Select all guestIDs that have the person's first name as 'John', and have a booking with an undefined dateTo. No explicit or implicit joins are allowed.
So far, I've done it using explicit joins to see how I might be able to avoid using joins. I don't know how to not use joins for this query, so that's what I'm asking for help on firstly.
My code so far:
```
SELECT GUEST.GUESTID FROM GUESTINNER JOIN BOOKING ON BOOKING.GUESTID=GUEST.GUESTID WHERE BOOKING.DATETO IS NULLAND GUEST.GUESTNAME='John%';
```
Select the cost and roomID of all rooms at the motel named 'Jollife'. If the room is occupied (according to the database), state the guestName of the room occupier.
All dates are in the format yyyymmdd. My code so far:
```
SELECT COST.PRICE, ROOM.ROOMID FROM ROOMINNER JOIN MOTEL ON MOTEL.MOTELID=ROOM.MOTELIDFULL OUTER JOIN BOOKING ON ROOM.MOTELID=BOOKING.MOTELID
```
I've attempted to start this question by doing an inner join of 'ROOM' and 'MOTEL' based on the motelID, and then a full outer join with 'BOOKING' so that the 'dateTo' can be checked. I'm not sure where to go from here. This is in postgresql.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

For (1):

SELECT GUEST.GUESTID 
FROM GUEST
WHERE GUEST.GUESTNAME='John %' /*space added because don't want name 'Johnathan' or 'Johnny'*/ AND
    EXISTS(SELECT 1 FROM BOOKING WHERE BOOKING.GUESTID=GUEST.GUESTID AND BOOKING.DATETO IS NULL)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101095 More actions · Answer 3

Just now noticed the Postgre sql qualification. I answered for SQL Server; code may or may not work on Postgre.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".