Match Multiple values with multiple rows

[siddhesh.kerkar]

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...

[smantscheff]

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'

[siddhesh.kerkar]

Thanx for your reply.

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

[smantscheff]

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.

[siddhesh.kerkar]

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