How can I select multiple values for one id in a multitable join

I have a SQL Server database of hotels, and 3 tables of criteria, I need to be able to select hotels which meet the criteria.
Hotels contains Hotelid, Name etc
table a contains hotelid, 'Type of Activity' (ie Beach, NightLife etc)
table b is similar to 'a' BUT holds spa info (ie detox, weight loss etc)
table c again similar to 'a' BUT holds Traveller info (i.e Solo Traveller, Honeymoons etc)

I want to create a storeprocedure that allows me to select the Hotelid and name which meet the selected criteria ie Hotels that have Beach, Nightlife, detox and honeymoons etc

I can get it to work for single selections from each table BUT get no data returned when I try to add two or more selections from either tables a, b or c

HELP!!
markejAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
Why don't you join the tables together in either a Union or Join. If you need a stored procedure, how do you expect the sp would work? What would be the parameters you pass.
0
Steve HoggITCommented:

SELECT HotelID FROM
(
SELECT HotelID, Activity Attribute FROM TableA
UNION ALL
SELECT HotelID, SpaInfo Attribute FROM TableB
UNION ALL
SELECT HotelID, TravInfo Attribute FROM TableC
)
WHERE Attribute = 'Pool'
  AND Attribute = 'FreeMoneyAtCheckIn'
  AND Attribute = 'Spaz'

Open in new window

0
ErnariashCommented:
Hope this will help....

SELECT * 
FROM Hotels H
 LEFT OUTER JOIN tableb A
ON A.hotelid = H.hotelid
  AND A.TypeofActivity like @TypeofActivity
LEFT OUTER JOIN tableb B
ON H.hotelid = B.hotelid
  AND B.spa like @spa
LEFT OUTER JOIN tablec C
ON H.hotelid = B.hotelid
	B.Traveller like @TravellerInfo
WHERE (H.Hotelid = @hotelid)

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

markejAuthor Commented:
Not sure what you mean a union?
I have the following:-
SELECT        HotelDetails.id, HotelDetails.HotelName, Lnk_Hotel_Activities.DescId AS activity, Lnk_Hotel_Essentials.DescId AS travellerInfo,
                         Lnk_Hotel_Spas.DescId AS Spa
FROM            HotelDetails INNER JOIN
                         Lnk_Hotel_Spas ON HotelDetails.id = Lnk_Hotel_Spas.id INNER JOIN
                         Lnk_Hotel_Essentials ON HotelDetails.id = Lnk_Hotel_Essentials.id INNER JOIN
                         Lnk_Hotel_Activities ON HotelDetails.id = Lnk_Hotel_Activities.id
WHERE        (Lnk_Hotel_Activities.DescId = 1 AND Lnk_Hotel_Activities.DescId = 2) AND (Lnk_Hotel_Essentials.DescId = 1) AND (Lnk_Hotel_Spas.DescId = 2)

I've attached samples of the data for the Hotel table and the lnk_Hotel_Activities, the other two tables have exactly the same structure as the lnk_Hotel_Activities table
id, Hotelname
1	intercontinental aphrodite hills resort
2	le meridien limassol resort & spa
3	anassa
4	elysium
5	thalassa
 
Lnk_Hotel_Activities
HotelId, Descid
1	5
1	1
1	4
4	1
5	6
3	1
2	1
1	2
NULL	NULL

Open in new window

0
ErnariashCommented:
Could you give more info?...Are there 4 tables?
Do you need all data criteria to be true to select your hotels?

SELECT * 
FROM Hotels H
 LEFT OUTER JOIN tableb A
ON A.hotelid = H.hotelid
  AND A.TypeofActivity like @TypeofActivity
LEFT OUTER JOIN tableb B
ON H.hotelid = B.hotelid
  AND B.spa like @spa
LEFT OUTER JOIN tablec C
ON H.hotelid = C.hotelid
	C.Traveller like @TravellerInfo
WHERE (H.Hotelid = @hotelid)

Open in new window

0
markejAuthor Commented:
Hi Ernarish,
Yes there are 4 tables Lnk_Hotel_Activities, Lnk_Hotel_Spas, Lnk_Hotel_Essentials and the hotel table  HotelDetails. The join is on HotelDetails.id = hotelid in the lnk_ tables.

The criteria for the activities table are:-
Beach
Diving & Watersports
Fishing
Golf & tennis
Night life
Pilates & yoga
Shopping

The user has the option of selecting which of those items they are interested in, i.e. it could be They want hotels that offer Fishing and shopping. SO I have to show ALL hotels that have both Shopping and fishing

I hope that clarifies it

Mark
0
ErnariashCommented:
Mark, base in your query...
Please your where has Lnk_Hotel_Activities.DescId = 1 AND Lnk_Hotel_Activities.DescId = 2 you only can have one Lnk_Hotel_Activities per line...
if you need more than one criteria use WHERE  Lnk_Hotel_Activities.DescId in (1, 2 )
WHERE        (Lnk_Hotel_Activities.DescId = 1 AND Lnk_Hotel_Activities.DescId = 2) AND (Lnk_Hotel_Essentials.DescId = 1) AND (Lnk_Hotel_Spas.DescId = 2)

SELECT        HotelDetails.id, HotelDetails.HotelName, Lnk_Hotel_Activities.DescId AS activity, Lnk_Hotel_Essentials.DescId AS travellerInfo, 
                         Lnk_Hotel_Spas.DescId AS Spa
FROM            HotelDetails INNER JOIN
                         Lnk_Hotel_Spas ON HotelDetails.id = Lnk_Hotel_Spas.id INNER JOIN
                         Lnk_Hotel_Essentials ON HotelDetails.id = Lnk_Hotel_Essentials.id INNER JOIN
                         Lnk_Hotel_Activities ON HotelDetails.id = Lnk_Hotel_Activities.id
WHERE        
    Lnk_Hotel_Activities.DescId in (1, 2 )
AND Lnk_Hotel_Essentials.DescId = 1 
AND Lnk_Hotel_Spas.DescId = 2

Open in new window

0
markejAuthor Commented:
Ernariash,
Thanks for that BUT when I try and run it I get no data if I have more than ONE of the lnk_ tables in the where clause, I have added entries for ALL the Essentials and Spas and Activities for 1 hotel to check it

Mark
0
Steve HoggITCommented:
Did you try the UNION ALL stmt I provided above. Not sure why that is not exactly what you are looking for?
0
ErnariashCommented:
Please you can not run more than one ONE of the same lnk_ tables in the where clause unless you use IN () or OR :
Your Query should run fine.
 Please try to run it without the WHERE then from the result  Lnk_Hotel_Activities.DescId, Lnk_Hotel_Essentials.DescId and Lnk_Hotel_Spas.DescId use it on your WHERE
Please check the data. The hotel you mentioned and run the query independently like:
 select * from Lnk_Hotel_Activities where DescId =1 and id = @yourID
select * from Lnk_Hotel_Essentials.DescId = 1 and id = @yourID
select * from Lnk_Hotel_Spas.DescId = 2 and id = @yourID

SELECT        HotelDetails.id, HotelDetails.HotelName, Lnk_Hotel_Activities.DescId AS activity, Lnk_Hotel_Essentials.DescId AS travellerInfo, 
                         Lnk_Hotel_Spas.DescId AS Spa
FROM            HotelDetails INNER JOIN
                         Lnk_Hotel_Spas ON HotelDetails.id = Lnk_Hotel_Spas.id INNER JOIN
                         Lnk_Hotel_Essentials ON HotelDetails.id = Lnk_Hotel_Essentials.id INNER JOIN
                         Lnk_Hotel_Activities ON HotelDetails.id = Lnk_Hotel_Activities.id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
markejAuthor Commented:
Ernariash,
Thanks for the help, I have now got it working, this is my final SQL statement:-
SELECT        HotelDetails.id, HotelDetails.HotelName, HotelDetails.CountryId, HotelDetails.HeaderImg1,
Lnk_Hotel_Activities.DescId AS activity, Lnk_Hotel_Essentials.DescId AS travellerInfo, Lnk_Hotel_Spas.DescId AS Spa, Countries.CtryName
FROM HotelDetails
INNER JOIN Lnk_Hotel_Spas ON HotelDetails.id = Lnk_Hotel_Spas.Hotelid
INNER JOIN Lnk_Hotel_Essentials ON HotelDetails.id = Lnk_Hotel_Essentials.Hotelid
INNER JOIN Lnk_Hotel_Activities ON HotelDetails.id = Lnk_Hotel_Activities.Hotelid
INNER JOIN Countries ON HotelDetails.CountryId = Countries.id
WHERE (Lnk_Hotel_Essentials.DescId IN
(SELECT Typeid FROM Tmp_SearchCriteria WHERE (Type = N'Essential')))
AND (Lnk_Hotel_Activities.DescId IN (SELECT Typeid FROM Tmp_SearchCriteria WHERE (Type = N'Activity')))  
AND (Lnk_Hotel_Spas.DescId IN (SELECT Typeid FROM Tmp_SearchCriteria WHERE (Type = N'spa')))  


Mark
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.