File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes SQL statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL statements" Watch "SQL statements" New topic

SQL statements

H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172

I've got a little trouble with SQL! I dont know if the cause is MS Access
My SQL statement is supposed to return the data in 3 columns, these columns are in different tables... Sometimes a table can be empty... and due to this, rather than the SQL returning the values in the other 2 table (which contain some data), it returns nothing! and i wonder why thats happening! why cant it just return the available data!?

My sql looks something like "SELECT Table1.Column1, Table2.Column2, Table3.column3 FROM Table1, Table2, Table3"

Any ideas folks?
Thnk u
[ February 28, 2006: Message edited by: Bear Bibeault ]
Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42965
For starters, are you sure that you don't need to include a WHERE clause that cuts down on the number of rows returned? As it is, you'll get a result that includes size(table1) * size(table2) * size(table3) rows.

As to the non-existing value, research the concept of "outer joins", which will cause the DB to return values from several tables even though one of them does not have a value.
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

why cant it just return the available data!?

There is nothing wrong with Access, it is probably returning exactly what you ask for. If you are joining three tables and one of the tables has no data then there is nothing to join on. You can use an outer join if you are joining related tables where the foreign key is nullable. However, your SQL statement does not include any join conditions. Is this deliberate? Are the table completely unrelated? If they are then you will need three seperate select statements.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Consider Paul's rocket mass heater.
subject: SQL statements
It's not a secret anymore!