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?
 
ErnariashConnect With a Mentor Commented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:

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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
 
HoggZillaCommented:
Did you try the UNION ALL stmt I provided above. Not sure why that is not exactly what you are looking for?
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.