List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 4 1999 5:43pm
Subject:Re: problem contructing query
View as plain text  
On Fri, 1999-09-03 14:39:38 -0700, Daryl Williams wrote:
> i have the following information in a table called availability:
> 
> room_number   room_type   day1   day2   day3   day4   day5
> 101E          ES4         Y      Y      N      N      Y
> 102E          ES4         N      N      N      N      N
> 
> and i would like to get back a list of the room(s) available on
> day1 through day3. i thought the following query would work:
> 
> SELECT * FROM availability
> WHERE room_number='102E' OR room_number='101E'
> AND availability.Day1='Y' AND availability.Day2='Y'
> AND availability.Day3='Y'
> ORDER BY 'room_type'

1st problem I see:
  The AND operator has greater precedence than OR, so
     a OR b AND c  is the same as   a OR ( b AND c ).
  What you probably want is:  ( a or b ) AND c
  You need parenthesis around the OR term!

2nd, I don't understand why you're testing for explicit room_numbers
  at all?  I thought you're looking for matching room number ...?
  So just leave out the test for specific room_number!

3rd, don't put quotes around the sorting field at ORDER BY.  If it
  works this way, this is by chance, but correctly it without quotes.

4th, a note:  to make SQL queries more clear, you can use table alias
  names if you like.

So I guess, a better query for your problem is:

  SELECT * FROM availability AS a
  WHERE a.Day1 = 'Y'
    AND a.Day2 = 'Y'
    AND a.Day3 = 'Y'
  ORDER BY room_type;


Did my explanations help?

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
problem contructing queryDaryl Williams4 Sep
  • Re: problem contructing queryMartin Ramsch4 Sep
Re: problem contructing queryDaryl Williams7 Sep