ResultSet returns empty when one of the tables is empty!

[H Melua]

Hello there

OK, i'm using MS Access database with my Java application...
i'm having trouble wording my SQL statement! I'm trying to get a resultSet holding 2 columns from different tables... but i get an empty resultsSet if one of the tables is empty! I want it to return either both or one of the columns... (in other word) if there is a value in any column then it should return it!

i tried this SQL and variations of it but its not working

"SELECT car, driver FROM DriverTable, CarTable WHERE (car IS NOT NULL) AND (driver IS NOT NULL)"

Any suggestion? the columns are not supposed to be linked...i just want the full values in both (or one, what ever thats available)

Thanks
HannaH
[ April 03, 2006: Message edited by: H Melua ]

[stu derby]

Originally posted by H Melua:
i'm having trouble wording my SQL statement! I'm trying to get a resultSet holding 2 columns from different tables... but i get an empty resultsSet if one of the tables is empty! I want it to return either both or one of the columns... (in other word) if there is a value in any column then it should return it!

i tried this SQL and variations of it but its not working

"SELECT car, driver FROM DriverTable, CarTable WHERE (car IS NOT NULL) AND (driver IS NOT NULL)"

Any suggestion? the columns are not supposed to be linked...i just want the full values in both (or one, what ever thats available)

Yes. The SQL is returning what it is supposed to under that circumstance. You have asked the database for the Cartesian product with a cross join of Drivertable and CarTable, and that product is always empty when one of the tables is empty. Your WHERE clause is then being applied to that empty result.

You probably don't really want a Cartesion product at all. In a Cartesion product of tables A and B with sizes m and n, respectively, your result is
of size m x n; each of the m rows of A is repeated n times with the n values of B. Unless you want all possible combinations of car and driver, you shold be trying something else; if you do want all possible combinations of car and driver, that's just what it gives you...

See:
http://en.wikipedia.org/wiki/JOIN

You don't say what you're trying to do, but I'm guessing that you really just want the 2 lists of cars and drivers. You should do that in 2 different queries. There ARE ways to do it in one query, but they're messy and there's no particular good reason for using them.

[H Melua]

Thank you very much stu

UNION is the answer for those who'd like to know
[ April 03, 2006: Message edited by: H Melua ]