[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-19
11
Medium Priority
?
192 Views
Last Modified: 2012-05-05
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!!
0
Comment
Question by:markej
  • 4
  • 4
  • 3
11 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23000003
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23000034

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
 
LVL 9

Expert Comment

by:Ernariash
ID: 23000076
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:markej
ID: 23000081
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 23000104
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
 

Author Comment

by:markej
ID: 23000160
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 23000205
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
 

Author Comment

by:markej
ID: 23000256
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23000415
Did you try the UNION ALL stmt I provided above. Not sure why that is not exactly what you are looking for?
0
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 23000490
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
 

Author Closing Comment

by:markej
ID: 31518535
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question