Hi everybody,
I work with the DBF_JDBC-Driver (20.11.2008) and there might be a problem.
I have two tables t1 and t2. If I make a Left Join and no row matches the condition, in some cases the ResultSet is empty. Unfortunately this is not in all cases. Maybe you have an idea, if this is a bug or my fault.
Here is a small example:
CREATE TABLE t1 (number INT);
CREATE TABLE t2 (number INT, fromDate Date, tillDate Date);
INSERT INTO t1 (number) VALUES (1);
INSERT INTO t2 (number, fromDate, tillDate) VALUES (1, '2008-11-11', '2008-12-12');
SELECT * FROM t1 LEFT JOIN t2 on 1=1;
=> ResultSet
---------------------------------------------------
| t1.number | t2.number | fromDate | tillDate |
| 1 | 1 | 2008-11-11 | 2008-12-12 |
---------------------------------------------------
SELECT * FROM t1 LEFT JOIN t2 on 1=2;
=> ResultSet is empty
But I expect the ResultSet
---------------------------------------------------
| t1.number | t2.number | fromDate | tillDate |
| 1 | null | null | null |
---------------------------------------------------
Am I right?
Now I use some statements, that are more realistic and also create an unexpected empty ResultSet:
SELECT * FROM t1 LEFT JOIN t2 on t1.number=t2.number AND CURRENT_DATE BETWEEN t2.fromDate AND t2.tillDate;
SELECT * FROM t1 LEFT JOIN t2 on t2.number=2;
SELECT * FROM t1 LEFT JOIN t2 on t1.number=2;
Last but not least a statement, that creates the expected ResultSet (not empty)
SELECT * FROM t1 LEFT JOIN t2 on t1.number!=t2.number
What do you think?
|
>SELECT * FROM t1 LEFT JOIN t2 on 1=1;
It should be SELECT * FROM t1 LEFT JOIN t2 on t1.number=t2.number;
>SELECT * FROM t1 LEFT JOIN t2 on 1=2;
>But I expect the ResultSet
It should be SELECT * FROM t1 LEFT JOIN t2 on t1.number!=t2.number;
>SELECT * FROM t1 LEFT JOIN t2 on t2.number=2;
It should be ELECT * FROM t1 LEFT JOIN t2 on t1.number=2;
>SELECT * FROM t1 LEFT JOIN t2 on t1.number=2;
It should be SELECT * FROM t1 LEFT JOIN t2 on t1.number=1;
>SELECT * FROM t1 LEFT JOIN t2 on t1.number=t2.number AND CURRENT_DATE BETWEEN t2.fromDate AND t2.tillDate;
It should be SELECT * FROM t1 right JOIN t2 on t1.number=t2.number AND CURRENT_DATE BETWEEN t2.fromDate AND t2.tillDate;
|
Thanks for the answer. But it doesn't solve my problem.
Most of the statements where just examples.
In my case, I need this statement:
>>SELECT * FROM t1 LEFT JOIN t2 on t1.number=t2.number AND CURRENT_DATE BETWEENt2.fromDate AND t2.tillDate;
>It should be SELECT * FROM t1 right JOIN t2 on t1.number=t2.number AND CURRENT_DATE BETWEEN t2.fromDate AND t2.tillDate;
1) Why do you use RIGHT JOIN? I want to see at least all entries from t1.
2) Even with RIGHT JOIN I get an empty ResultSet?!?
|
>1) Why do you use RIGHT JOIN? I want to see at least all entries from t1.
Then you should use LEFT JOIN, but a condition on t2 will filter all unsatisfied rows.
|
> but a condition on t2 will filter all unsatisfied rows.
This is how DBF_JDBC works. But it's not what I expect when I use LEFT JOIN.
As a workaround, I use
select * from t1 LEFT JOIN t2 on t1.number=t2.number AND t2.fromDate<=CURRENT_DATE OR t2.fromdate IS NULL) AND (CURRENT_DATE<=t2.tilldate OR t2.tilldate IS NULL);
I don't know why, but it works in my tested cases.
|
|
Yeah. You can use t2.tilldate=null too.
|
Main
webmaster@hxtt.com