What should the following query
In answer to your questions:
Case 1:
You have no data in table 2, the result of any join with this table will return NULL
because there are NO ROWS in the table.
If you want it to return the single value from table 1, do this "From t1, outer t2.
Case 2:
See comment above, there is now a row in the table, so an appropraite comparison can be
made.
Case 3:
This is also correct. You now have 2 rows in table 2. The reason this happens is that you
have 2 rows in table 2 being JOINED with the SAME row in table 1. Both rows get returned
because they both satisfy the requirement t1.name = 'Hen'.
Your query would produce a cartesian result because you do not have an explicit join
between the 2 tables.
You need to change your query:
select t1.license_no
from tb1 t1, outer tb2 t2
where t1.name = 'Hen'
and t1.licence_no = t2.licence_no
or ( t2.country = 'Mars' and t2.state = 'Colorado' )
I have not tried it, but believe this is what you wanted to achieve.
Mark
MarkDenham@aol.com (home)
Denham.M@amstr.com (work)
--------------------------( Forwarded letter 1 follows )---------------------
Date: Thursday, 5 June 1997 11:18am
From: Shubhasheesh Anand <anand@jwala.engr.sgi.com>
Subject: What should the following query return
Date: Thu, 05 Jun 1997 10:00:19 -0700
Reply-To: anand@jwala.engr.sgi.com
Organization: Silicon Graphics Inc., Mountain View, CA
Sender: informix-list-owner@rmy.emory.edu
To: informix-list@rmy.emory.edu
X-Informix-List-Id: <news.38807>
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