Re: What should the following query return

[ Original included for completion ] Try adding the word OUTER before tb2. Oh, and why alias tb2 to t2, out of curiosity? tb2 works throughout and is easier to read... :=) -Richard On Thu, 5 Jun 1997, Shubhasheesh Anand wrote: > Hi, > > I have a query that does not seem to behave the way I > think it should !! > > Please see if you can help in pointing out the problem. > > I have the following schema: > > CREATE TABLE tb1 > ( > license_no INT NOT NULL, > name VARCHAR(250) NOT NULL > );> > CREATE TABLE tb2 > ( > license_no INT NOT NULL, > country VARCHAR(100) NOT NULL, > state VARCHAR(100) NOT NULL > );> > > And I insert the following data: > > INSERT INTO tb1 values ( 1, 'Hen' );> > SELECT t1.license_no > from tb1 t1 , tb2 t2 > where t1.name = 'Hen' or ( t1.license_no = t2.license_no and t2.country> = > 'Mars' and t2.state = 'Colorado' ) > > The above query does not return anything, though I think it > should return the row in tb1. > > Subsequently, if I insert the following: > > INSERT INTO tb2 values (3, 'Jupiter', 'Kansas');> > The same query DOES correctly return the row: > > license_no > 1 > > Now if I insert the following : > INSERT INTO tb2 values (4, 'Saturn', 'Texas' );> > and run the same query, I get the same row, but twice: > > license_no > 1 > 1 > > Though I think it should return it only once. > > Thanks > Shubhasheesh > >