This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes ResultSet returns empty when one of the tables is empty! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ResultSet returns empty when one of the tables is empty!" Watch "ResultSet returns empty when one of the tables is empty!" New topic
Author

ResultSet returns empty when one of the tables is empty!

H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
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
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
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
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
Thank you very much stu

UNION is the answer for those who'd like to know
[ April 03, 2006: Message edited by: H Melua ]
 
Consider Paul's rocket mass heater.
 
subject: ResultSet returns empty when one of the tables is empty!
 
Similar Threads
SQL statements
MS Temporary tables and metadata
How do I store an Access Memo field in a Hashtable
Getting table name for column
Columns on JTable