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_SER VICES
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 2 1907
"Robert" <fo***@freemail .nl> wrote in message
news:10******** *****@corp.supe rnews.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_SER VICES 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_ser vices hs
where h.hotel_id=hs.h otel_id and hs.service_id=1 and
hs.service_valu e=1)
and exists (select hs.fk_hotel_id from link_hotels_ser vices hs
where h.hotel_id=hs.h otel_id and hs.service_id=2 and
hs.service_valu e=1)
The above is untested so the syntax may be a little off but I think you get
the idea
"Ronnie Chee" <cheer@t[127.0.0.1]> wrote in message
news:41******@n ews.nucleus.com ... "Robert" <fo***@freemail .nl> wrote in message news:10******** *****@corp.supe rnews.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_SER VICES 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_ser vices hs where h.hotel_id=hs.h otel_id and hs.service_id=1 and hs.service_valu e=1) and exists (select hs.fk_hotel_id from link_hotels_ser vices hs where h.hotel_id=hs.h otel_id and hs.service_id=2 and hs.service_valu e=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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Bodza Bodza |
last post by:
I'm having an argument with an incumbent self-taught programmer that
it is OK to use null foreign keys in database design.
My take is the whole point of a foreign key is that it's not supposed
to be optional, it's very definition is it's a necessary link to the
parent table and part of the definition. If it's optional it shouldn't
be part of the definition of a table and should be in a linking table
instead.
Comments?
|
by: pb648174 |
last post by:
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.
Is there any way to specify a foreign key that will allow for the
different types indicating which table the...
|
by: D. Dante Lorenso |
last post by:
I'd like to run a clean up command on my tables to
eliminate rows that I'm no longer using in the database.
I want to do something like this:
DELETE FROM tablename
WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE;
Does anyone know how something like this could be done
in PostgreSQL? I know I can search all the tables that
|
by: DaveDiego |
last post by:
I've had a user delete one of the client records, I do have a version of
the DB with all records intact before the deletion occured.
Whats the best approach to getting all the related records in each of
the tables? I have about 12 tables to put data back into and multiple
records for each. Would I need to make an append or update query for each
table?
|
by: am72de |
last post by:
Hi all,
I have the following tables:
Create Table T1( ID1 int Not Null, ID2 int Not Null, Description
VarChar(20), Constraint ID Primary Key(ID1, ID2) )
Create Table T2( ID1 int Not Null, ID2 int Not Null, Description
VarChar(20), Constraint ID Primary Key(ID1, ID2) )
| |
by: Jim Devenish |
last post by:
I have a table in which a number of records get lost, most likely after
compacting the database.
The relevant table structure is as:
Table Tasks with a primary key TaskID (Autonumber)
Table Enquiries with primary key EnquiryID and foreign key TaskID (long
integer)
A relationship between the tables (on TaskID) includes cascading
|
by: Allen Anderson |
last post by:
Hi,
I'm trying to design contact (names and addresses) tables in an Access
database. Some of the contacts represent vendors, some are board members of
the organization, some are donors, some are neighbors of the organization,
some are politicians, etc. Rather than create separate tables for each type
of contact, I thought it would be better to have:
one table with names/addresses
one table with kinds of lists (vendors, board...
|
by: Rex |
last post by:
In a table, I have number of records belonging to a particular ID now
if I enter a value in one of the fileds of this table I want it to be
copied in all the records belonging to this particluar ID. for example
ID Name
--------------------------
1 xyz
1
1
1
|
by: Eric IsWhoIAm |
last post by:
I have four tables created so far: Courses, Instructors, Courses and
Instructors (which shows the Course and Instructor Name fields, but holds
their IDs since those are the keys), and Students. Now, I wish to create a
Classrooms (or something similar) table which will allow me to pick the
Course from Courses and Instructors, and hold multiple Students for each
Course. I am unsure how to do this in Access.
Each student can have multiple...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |