What should the following query return
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