469,649 Members | 1,171 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,649 developers. It's quick & easy.

Selecting records where multiple foreign keys are a certain value

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
2 1746
"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
"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.

Similar topics

10 posts views Thread by Bodza Bodza | last post: by
3 posts views Thread by Jim Devenish | last post: by
13 posts views Thread by Eric IsWhoIAm | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.