0

Hi,

I have table structure as follows

Table Name- facilities

facility_id | hotel_code | facility_name
1 1 Parking
2 1 AC
3 2 Parking
4 3 Parking
5 3 AC
6 4 AC

Now i have to find the 'hotel_code' that having 'Parking' and 'AC' both facilities ie 'hotel_code' as 1 and 3.

I hv tried following query

SELECT hotel_code FROM facilities WHERE facility_name IN ('Parking','AC') GROUP BY hotel_code

returns
1
2
3
4

Plz give some suggestion...

2
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by siddhesh.kerkar
0

Your where clause is in effect the same as facility_name = 'Parking' or facility_name = 'AC' But what you need is facility_name = 'Parking' [U]and[/U] facility_name = 'AC'

0

Thanx for your reply.

I hv tried your query but got empty result set, as 'Parking' and 'AC' are not in single row.

Edited by siddhesh.kerkar: n/a

0

Of course, you have to adapt the logic to your query.
Or you can join the hotel table to itself on the hotel_code:

SELECT distinct a.hotel_code FROM facilities a, facilities.b 
WHERE a.hotel_code = b.hotel_code
AND a.facility_name='Parking' and b.facility_name='AC'

But I assume this comes not before lesson 7.

0

The following query worked for me...

SELECT hotel_code 
FROM facilities 
WHERE facility_name IN ('Parking','AC') 
GROUP BY hotel_code 
HAVING COUNT(*)=2

tnkx

Edited by siddhesh.kerkar: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.