Sequel to get select EVERY input

Question

I have created three tables

CREATE TABLE guest(

name varchar(100),
ranking int,
PRIMARY KEY (name)
);

CREATE TABLE room(

 roomname varchar(100),
 wallcolor varchar(100),
 rating int,
 PRIMARY KEY(roomnane)
 );

 CREATE TABLE reservation(

 name varchar(100),
 roomname varchar(100),
 day varchar(100),
 moveinday int,
 moveoutday int,
 PRIMARY KEY(roomname, day, start, finish),
  FOREIGN KEY(roomname) REFERENCES room(roomname),
  FOREIGN KEY(name) REFERENCES guest(name)
 );

I am trying to write a query to find the guests who reserved EVERY "LakeView" room

I tried the following

SELECT g.name
FROM guest g, reservation r, room rr
WHERE rr.name = g.name
AND rr.roomname = "LakeView"
GROUP BY g.name

This does not seem to list out every room. How could I gix this?

Input:

  insert into guest values ('Andrew', 1);
  insert into guest values ('Jack', 4);
  insert into guest values ('Jake', 4);
  insert into room values ('LakeView', 'white', 10);
  insert into room values ('BayView', 'blue', 4);
  insert into reservation values ('Andrew', 'LakeView', 'Friday', 10,15);
  insert into reservation values ('Jake', 'LakeView', 'Monday', 10,16);
  insert into reservation values ('Jack', ' BayView', 'Tuesday', 11,15);

Desired output is

   Andrew 
   Jake

What did you actually mean by this term **who reserved EVERY "LakeView" boat** ? — 1000111, Feb 03 '16 at 06:19
that was a typo. check the edit for sample input and expected output — Jonathan Bishop, Feb 03 '16 at 06:24
look it's wrong probably **WHERE rr.name = g.name**. you are matching the roomname with guest name. — 1000111, Feb 03 '16 at 06:30
What do you mean by `"EVERY "LakeView" room"? Is there more than one LakeView room, and how do we tell if a guest has reserved them all? — Barmar, Feb 03 '16 at 06:53
And how could there be more than one, if `roomname` is the primary key? That means it has to be unique. — Barmar, Feb 03 '16 at 06:54
i want to know every guest that reserved a lakeview room. its just a type ofroom. i could have 5 lakeview rooms. i want to know all 5 guests who reserved it, if 5 have reserved it — Jonathan Bishop, Feb 03 '16 at 06:55

score 1 · Answer 1 · edited May 23 '17 at 11:45

1

try this:

Solution 1:

SELECT 
guest.name
FROM guest INNER JOIN 
reservation ON reservation.name = guest.name
INNER JOIN room ON reservation.roomname = room.roomname
WHERE reservation.roomname = "LakeView"
GROUP BY guest.name

Here I've highlighted your mistakes:

You tried to match rr.name with g.name. That means you are matching roomname with guest name which you didn't want (so far I guess).
Another mistake is you are only checking "LakeView" only in room table entries. So other guests who haven't reserved LakeView room might appear in the final result set. You also need to filter those records from reservation table which have LakeView roomname.

Here's your query modified based on the above observations:

Solution 2:

SELECT g.name
FROM guest g, reservation r, room rr
WHERE r.name = g.name
AND rr.roomname = "LakeView"
AND r.roomname = "LakeView"
GROUP BY g.name;

N:B: Solution 1 is encouraged to use. Try to avoid implicit joins which are stated in solution 2.

I personally think the INNER JOIN is better, because it is more readable. It shows better the relations between the table. You got those relations in the join, and you do the filtering in the WHERE clause. This separation makes the query more readable.

Have a look at this post.

edited May 23 '17 at 11:45

Community

1
1

answered Feb 03 '16 at 06:31

1000111

13,169
2
28
37

1

Please don't encourage the user of implicit joins. Use ANSI `JOIN ... ON` syntax in answers. – Barmar Feb 03 '16 at 06:49
You are right @Barmar. But I just wanted to help him the way he approached. Later I can add suggestion on building a query USING ANSI `JOIN....ON`. Thanks. – 1000111 Feb 03 '16 at 06:51
this seems to only return 1 guest who reserved. i want to know every guest that reserved a lakeview room – Jonathan Bishop Feb 03 '16 at 06:55
check your guest table whether **Jake** exists or not. – 1000111 Feb 03 '16 at 06:56
in your sample input there's no entry for **Jake**. – 1000111 Feb 03 '16 at 06:56
I've updated the query based on INNER JOIN as Barmar pointed out. Please check this query. @JonathanBishop – 1000111 Feb 03 '16 at 07:02

score 0 · Answer 2 · answered Feb 03 '16 at 06:28

0

if you using php-mysql then it will be help

$qry=mysql_query("select name from reservation where roomname='LakeView'");
while($result=mysql_fetch_array($qry)
{
  echo $result['name'];
 }

answered Feb 03 '16 at 06:28

Dinesh Gurjar

1

What does PHP have to do with this? The query is the same no matter what language is used. And you shouldn't use the obsolete `mysql` extension, use either `mysqli` or `PDO`. – Barmar Feb 03 '16 at 06:51

score 0 · Answer 3 · answered Feb 03 '16 at 06:28

I'm assuming that the 'name' column in the reservation table is the name of the guest or guests. If this is true it sounds like you only need the reservation table for the purposes of this query, but if you wanted to combine them all anyway:

select rv.name as Guest from room as r left join reservation as rv on rv.roomname = r.roomname left join guest as g on g.name = rv.name where rv.roomname = 'LakeView'

Sequel to get select EVERY input

3 Answers3