[rnty]

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

[William_Wilson]

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

[rnty]

Thank You