Selecting records where multiple foreign keys are a certain value

[Robert]

Hi All,

I'm trying to solve this for a few days now and I just can't figure it
out...

I have three tables set up, I'll simplify them for this question:

Table 1: HOTELS
Columns: HOTEL_ID, HOTEL_NAME
Exmple row: 123 || 'Hotel X'

Table 2: SERVICES
Columns: SERVICE_ID, SERVICE_NAME
Example rows:
1 || 'Breakfast in bed'
2 || 'King size bed'

Table 3: LINK_HOTELS_SERVICES
Columns: FK_HOTEL_ID, FK_SERVICE_ID, SERVICE_VALUE
Example rows:
123 || 1 || 1
123 || 2 || 1

In table 3 I link different services to different hotels. In the same table
I set the "value" for the service.

The first example row of table 3 means something like: Hotel X offers
Breakfast in bed. In this case 1 stands for TRUE

The second example row of table 3 means: Hotel X offers King size beds
(again: 1 stands for TRUE).

What I'm struggling with is selecting the hotel ID's which offer multiple
services. To stay in the example: how can I select all hotels where
SERVICE_ID = 1 AND SERVICE_ID = 1. I can't seem to figure out how to do
it...

I hope anyone can help... Thanks a lot in advance!!!

Robert

[Ronnie Chee]

"Robert" <fo***@freemail.nl> wrote in message
news:10*************@corp.supernews.com...

Hi All,

I'm trying to solve this for a few days now and I just can't figure it
out...

I have three tables set up, I'll simplify them for this question:

Table 1: HOTELS
Columns: HOTEL_ID, HOTEL_NAME
Exmple row: 123 || 'Hotel X'

Table 2: SERVICES
Columns: SERVICE_ID, SERVICE_NAME
Example rows:
1 || 'Breakfast in bed'
2 || 'King size bed'

Table 3: LINK_HOTELS_SERVICES
Columns: FK_HOTEL_ID, FK_SERVICE_ID, SERVICE_VALUE
Example rows:
123 || 1 || 1
123 || 2 || 1

In table 3 I link different services to different hotels. In the same table I set the "value" for the service.

The first example row of table 3 means something like: Hotel X offers
Breakfast in bed. In this case 1 stands for TRUE

The second example row of table 3 means: Hotel X offers King size beds
(again: 1 stands for TRUE).

What I'm struggling with is selecting the hotel ID's which offer multiple
services. To stay in the example: how can I select all hotels where
SERVICE_ID = 1 AND SERVICE_ID = 1. I can't seem to figure out how to do
it...

I hope anyone can help... Thanks a lot in advance!!!

Robert

Assuming what you really meant above was:
where SERVICE_ID = 1 AND SERVICE_ID = 2
you can do

select h.hotel_name
from hotels h
where exists (select hs.fk_hotel_id from link_hotels_services hs
where h.hotel_id=hs.hotel_id and hs.service_id=1 and
hs.service_value=1)
and exists (select hs.fk_hotel_id from link_hotels_services hs
where h.hotel_id=hs.hotel_id and hs.service_id=2 and
hs.service_value=1)

The above is untested so the syntax may be a little off but I think you get
the idea

[Robert]

"Ronnie Chee" <cheer@t[127.0.0.1]> wrote in message
news:41******@news.nucleus.com...

"Robert" <fo***@freemail.nl> wrote in message
news:10*************@corp.supernews.com...

Hi All,

I'm trying to solve this for a few days now and I just can't figure it
out...

I have three tables set up, I'll simplify them for this question:

Table 1: HOTELS
Columns: HOTEL_ID, HOTEL_NAME
Exmple row: 123 || 'Hotel X'

Table 2: SERVICES
Columns: SERVICE_ID, SERVICE_NAME
Example rows:
1 || 'Breakfast in bed'
2 || 'King size bed'

Table 3: LINK_HOTELS_SERVICES
Columns: FK_HOTEL_ID, FK_SERVICE_ID, SERVICE_VALUE
Example rows:
123 || 1 || 1
123 || 2 || 1

In table 3 I link different services to different hotels. In the same

table

I set the "value" for the service.

The first example row of table 3 means something like: Hotel X offers
Breakfast in bed. In this case 1 stands for TRUE

The second example row of table 3 means: Hotel X offers King size beds
(again: 1 stands for TRUE).

What I'm struggling with is selecting the hotel ID's which offer multiple
services. To stay in the example: how can I select all hotels where
SERVICE_ID = 1 AND SERVICE_ID = 1. I can't seem to figure out how to do
it...

I hope anyone can help... Thanks a lot in advance!!!

Robert

Assuming what you really meant above was:
where SERVICE_ID = 1 AND SERVICE_ID = 2
you can do

select h.hotel_name
from hotels h
where exists (select hs.fk_hotel_id from link_hotels_services hs
where h.hotel_id=hs.hotel_id and hs.service_id=1 and
hs.service_value=1)
and exists (select hs.fk_hotel_id from link_hotels_services hs
where h.hotel_id=hs.hotel_id and hs.service_id=2 and
hs.service_value=1)

The above is untested so the syntax may be a little off but I think you
get
the idea

Great! That's it! Thanks a lot!