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 HannaH [ February 28, 2006: Message edited by: Bear Bibeault ]
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.
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.