By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,827 Members | 813 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,827 IT Pros & Developers. It's quick & easy.

Selecting records where multiple foreign keys are a certain value

P: n/a
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
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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
Jul 23 '05 #2

P: n/a
"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!
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.