Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 13th, 2005, 10:56 AM
Registered User
 
Join Date: Dec 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Selecting on different values of the same field

This is a bit fiddly so I hope I can explain it clearly.

Simplifying things, (using mysql btw) I have a hotels table and a facilities table a bit like this;

** hotels table **

id, hotel name

1, best western
2, comfort inn
3, holiday inn

** facilities table

id, hotel id, facility id

1, 1, 2
2, 1, 3
3, 1, 7
4, 2, 2
5, 2, 7
6, 3, 5
7, 3, 6

What I am trying to do it pull out all hotels from a list
that have certain facilities, e.g. I want all hotels that have
facilities 2 and 7.

I've tried pretty much everything but am failing
miserably. This doesn't work (I know this is obviously wrong, but it
should show what I am trying to do);

SELECT name
FROM hotels, facilities
WHERE hotels.id = facilities.hotel_id
and (facility_id=2 and facility_id=7)

It would be easy if it could be OR, but I need only those hotels with
ALL the facilities specficied. I've tried HAVING and that hasn't worked either.

Can anybody offer any help please?

Many many thanks.
 
Old December 13th, 2005, 11:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try:

SELECT name
FROM hotels, facilities
WHERE hotels.id = facilities.hotel_id
AND facility_id IN (2, 7)

That's the preferred (and simpler).

Rand
 
Old December 13th, 2005, 11:12 AM
Registered User
 
Join Date: Dec 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by rgerald
 Try:

SELECT name
FROM hotels, facilities
WHERE hotels.id = facilities.hotel_id
AND facility_id IN (2, 7)

That's the preferred (and simpler).

Rand
Blimey that was quick, thanks. I did try that one before and again just now, this seems to return all hotels that have any facility numbered 2 OR 7. I need to to return those that only have 2 AND 7.

Thanks

Klaus

 
Old December 19th, 2005, 03:53 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to nalla Send a message via Yahoo to nalla
Default


Hi,

There are couple of solutions for this. Here is one of them. I hope this will resolve your problem

SELECT #hotels.hotel_name FROM #facilities f1 INNER JOIN #facilities f2
ON f1.hotel_id = f2.hotel_id INNER JOIN #hotels ON #hotels.id= f1.hotel_id
WHERE f1.facility_id=2 AND f2.facility_id=7

nalla


 
Old May 30th, 2006, 11:08 AM
Registered User
 
Join Date: Dec 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by nalla


Hi,

There are couple of solutions for this. Here is one of them. I hope this will resolve your problem

SELECT #hotels.hotel_name FROM #facilities f1 INNER JOIN #facilities f2
ON f1.hotel_id = f2.hotel_id INNER JOIN #hotels ON #hotels.id= f1.hotel_id
WHERE f1.facility_id=2 AND f2.facility_id=7

nalla


Apologies for the delay in replying, I got sidetracked onto other projects. I'm back on this now and just fixed my monster sql statement.

Your solution worked. Thanks very much.






Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL script for selecting min and max values Nancy Krause Need help with your homework? 0 April 12th, 2007 11:29 AM
get the values of form field values crmpicco Perl 2 March 16th, 2007 10:57 AM
Move focus to field without selecting text echovue Access VBA 1 April 12th, 2006 01:52 AM
XPath - Selecting nodes based on attribute values billy_bob_the_3rd XML 4 December 1st, 2004 06:12 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.