CODEWITHSUNDEEP
sqlsql-server
Jerry West
Jerry West

Reputation: 151

Can you use data that you got from a query in another query?

Say I've got some data from a SELECT query. Can I use this as a table later, meaning naming it something then using its rows and columns in other queries?

I can't solve this problem for the life of me. I'm a beginner. It's just one table but I just can't get a query working. Here is what I have:

Hotel table and Room table (I'll just need to use the Room table; I mentioned Hotel just as a reference point for understanding).

Room has the following columns: (Number,HID) - this is a composite primary key; Number is the numerical number of the room and HID is the ID of the hotel which it belongs to. I also have one more column, Name. Now the problem is:

Find all the Hotels which only have rooms Named OneBedroom

I tried (and failed) by doing it by selecting all HIDs from Room, then filtering on not exist(hotels that have at least one non-OneBedroom named room), but I couldn't make this work.

Room

Number    HID    Name

1         H1     OneBedroom
2         H2     OneBedroom
3         H1     OneBedroom
4         H1     OneBedroom
5         H2     TwoBedroom
6         H3     OneBedroom

Desired Output:    HID
           H1
           H3

Upvotes: 1

Views: 59

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The easiest way is using group by plus having with a conditional count, so no need to include aditional subquery.

This return hotel with all room = "OneBedroom", remember COUNT only count values <> NNLL

 SELECT `hid` 
 FROM `room`
 GROUP BY `hid`
 HAVING COUNT(CASE WHEN `name` != "OneBedroom" THEN 1 END ) = 0

At least one bedroom is call "OneBedroom"`

 HAVING COUNT(CASE WHEN `name`  = "OneBedroom" THEN 1 END ) >= 1

The default value for CASE is NULL so no need for ELSE part

Upvotes: 1

Stidgeon
Stidgeon

Reputation: 2723

I'm going with a table like this from what you describe:

table Room
HID    Number        Name
----------------------------------------
 1      101          OneBedroom
 1      102          OneBedroom
 1      103          TwoBedroom
 1      201          Suite
 2      101          OneBedroom
 2      102          OneBedroom
 2      103          OneBedroom

which I assume connects to a table Hotel:

table Hotel
HID    name
------------------
 1     StayOver
 2     SleepTite

Now for the query:

SELECT h.name 
FROM Hotel h
WHERE h.HID NOT IN (SELECT r.HID 
FROM Room r
WHERE name != 'OneBedroom');

which will give you a list of the hotel names with only one bedroom rooms.

Works on SQLite with sample data. Should be okay.

EDIT - based on edited question:

SELECT h.HID . . .

instead of h.name in above query.

Upvotes: 1

CollinD
CollinD

Reputation: 7573

I suspect something like this would do it

SELECT DISTINCT `hid` FROM `room` WHERE
    `room`.`hid` NOT IN
        (SELECT `hid` as `hid` FROM `room` WHERE `name` != "OneBedroom");

Untested, might be crap, but seems like it should work. Basically, the inner query gets all hid's of rooms that are not OneBedrooms. We then subtract all of those hid's from a full list of hid's.

Upvotes: 2

Related Questions