2 Replies - 3556 Views - Last Post: 24 August 2012 - 10:40 AM

#1 rnty  Icon User is offline

  • D.I.C Head

Reputation: 19
  • View blog
  • Posts: 168
  • Joined: 14-August 12

Corresponding queries?

Posted 20 August 2012 - 11:04 AM

SQL> select * from hotel;

       HNO NAME       ADDRESS
---------- ---------- ----------
         1 taj        mumbai
         2 ashoka     new dehli
         3 empire     bangalore
         4 meridian   chennai

SQL> select * from room;

       RNO        HNO TYPE            PRICE
---------- ---------- ---------- ----------
         1          1 double            800
         2          1 family           1600
         1          2 single            500
         2          2 double           1000
         1          3 double            300
         2          3 family            600
         1          4 double           1000
         2          4 family           2500

8 rows selected.

QUERY 1
-------
SQL> select h.name,r.rno,r.hno,r.type from hotel h inner join room r on price<80
0 and type in('double','family') and r.hno=h.hno;

NAME              RNO        HNO TYPE
---------- ---------- ---------- ----------
empire              1          3 double
empire              2          3 family

QUERY 2
-------
SQL> select h.name,r.rno,r.hno,r.type from hotel h,room r where price<800 and r.
hno=h.hno and type='double' or type='family';

NAME              RNO        HNO TYPE
---------- ---------- ---------- ----------
taj                 2          1 family
taj                 2          3 family
taj                 2          4 family
ashoka              2          1 family
ashoka              2          3 family
ashoka              2          4 family
empire              2          1 family
empire              1          3 double
empire              2          3 family
empire              2          4 family
meridian            2          1 family

NAME              RNO        HNO TYPE
---------- ---------- ---------- ----------
meridian            2          3 family
meridian            2          4 family




The first query gives me the correct output.The second query,which according to me, was an equivalent query displays a strange result.
The query is for selecting the hotel name,hotel no and room no of hotels where double or family room rent is less than 800.
Please explain to me what the second query is actually doing?
Grateful for your help.Thank you

Is This A Good Question/Topic? 0
  • +

Replies To: Corresponding queries?

#2 William_Wilson  Icon User is offline

  • lost in compilation
  • member icon

Reputation: 205
  • View blog
  • Posts: 4,807
  • Joined: 23-December 05

Re: Corresponding queries?

Posted 20 August 2012 - 03:51 PM

I haven't checked the logic of each piece, but for certain you will want to place type='double' or type='family' in brackets. Your last condition being an OR means it will ignore some or all of your and conditions if the type is family.

Thus why you get results of price that are over 800 when the type is family.

Edit: assume sql interpretation is stupid (because it is). It does not know what you meant, you must apply absolute segregation of conditional booleans.

This post has been edited by William_Wilson: 20 August 2012 - 03:53 PM

Was This Post Helpful? 1
  • +
  • -

#3 rnty  Icon User is offline

  • D.I.C Head

Reputation: 19
  • View blog
  • Posts: 168
  • Joined: 14-August 12

Re: Corresponding queries?

Posted 24 August 2012 - 10:40 AM

Thank You
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1