CODEWITHSUNDEEP
sqloracle-databaseselectdistinct
nj2012
nj2012

Reputation: 105

Retrieving Single unique record from multiple repeating record

If I have the following two tables

HOTELTABLE 

 HOTELID   HOTEL NAME
  1        PARK HOTEL
  2        PARK PLAZA
  3        HOLIDAY IN

ROOM TABLE

HOTELID    ROOMINUMBER  ROOMTYPE  FLOORNUMBER  ROOMRATE
1          111          SINGLE         1          100
1          111          DOUBLE         1          200
1          113          DOUBLE         2          200
2          111          FAMILY         2          300

I want to list the hotel name, room type and room rate for each hotel that contains the word PARK in its name, and if a room of the same type and rate at the same hotel I want to list it only once. The following is my code... Would I need a subquery, how can I go around this query?

SELECT DISTINCT HOTELNAME, 
       DISTINCT ROOMTYPE, 
       DISTINCT ROOMRATE
  FROM ROOM, 
       HOTEL
 WHERE HOTELNAME LIKE '%PARK%'
GROUP BY HOTEL.HOTELNAME, 
         ROOMTYPE;

THE OUT PUT SHOUD BE

HOTEL NAME      ROOMTYPE   ROOMMATE
PARK HOTEL       SINGLE     100
PARK HOTEL       DOUBLE     200
PARK PLAZA       FAMILY     300

Upvotes: 0

Views: 103

Answers (2)

woemler
woemler

Reputation: 7169

Another option:

SELECT DISTINCT 
    HOTELNAME, 
    ROOMTYPE, 
    ROOMRATE
FROM 
    ROOM 
    JOIN HOTEL
       ON ROOM.HOTEL_ID = HOTEL.HOTEL_ID
WHERE 
    HOTELNAME LIKE '%PARK%'

Upvotes: 2

samjudson
samjudson

Reputation: 56853

All items in your select must appear in the group by.

And you need to actually join the two tables together, based on the hotel id.

SELECT H.HOTELNAME, R.ROOMTYPE, R.ROOMRATE
FROM HOTEL H INNER JOIN ROOM R ON H,HOTELID = R.HOTELID
WHERE H.HOTELNAME LIKE '%PARK%'
GROUP BY H.HOTELNAME, R.ROOMTYPE, R.ROOMRATE

Upvotes: 1

Related Questions