wood burning stoves 2.0*
The moose likes JDBC and the fly likes Select statement error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Select statement error" Watch "Select statement error" New topic
Author

Select statement error

Chris Brat
Ranch Hand

Joined: May 22, 2003
Posts: 108
Hi all,
am trying to select information from a few tables, and because some of the tables have overlapping fieldnames e.g. name etc, I am trying to reference them in my sql statment like this...
sqlString = "select myTable1.name, myTable2.name from myTable1, myTable2 where .... and so on and so on
this unfortunately does not work and I am told that 'column not found'
My question is can fields be referenced by their table name?
If not, how can specific tables be referenced in a similar way if they have different schemas?
By the way my connection is with the JDBC DBC bridge.... could the bridge be my problem.

Thanks in advance
Chris


SCJP 1.2, SCJP 5, SCBCD
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

I don't see anything wrong with your partial SQL statement. Make sure your column is really "name" and not "Name" or "NAME". Column names are case sensitive. Also, try quering a sinle table and see if you get the same error message. If all this failes, show us your complete SQL statement followed by the complete error message. Sometimes those error messages aren't for the reasons you think.


GenRocket - A Test Data Generation Platform
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
Which databases have case-sensitive column names? Neither Oracle nor MySQL do...


Ron Newman - SCJP 1.2 (100%, 7 August 2002)
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

Originally posted by Ron Newman:
Which databases have case-sensitive column names? Neither Oracle nor MySQL do...

MS SQL does.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29241
    
139

You left out the table names. It should be:


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
You don't have to rename the tables in a SELECT statement.
Chris Brat
Ranch Hand

Joined: May 22, 2003
Posts: 108
Hi,
Jeanne , I did include my tables after the 'from'...
I tried the select from a single statement like,
select table1.column1 from table1 where column2 = 'somevalue'

and got the same error, but the following works
select column1 from table1 where column2 = 'somevalue'

suggestions?
Nagendra Prasad
Ranch Hand

Joined: Jul 11, 2002
Posts: 219
U have to have consistent aliasing of the column names.
In you where clause u should prefix the column with the 'tablename.'
in the last instance where u had success, there were no aliases used, and by default all the columns were 'bound' to the table in the FROM part of the sql statement.


Best Regards,<br />Nagendra Prasad.
 
 
subject: Select statement error
 
Similar Threads
MS Access - how to read query
Simple RDBMS question
Schema's in Grails
deleting rows with referential integrity
Is there a way to perform SQL queries on local data?