Thread: select on multiple tables
I apologize in advance if this isn't the right place to ask this. I have a query like "SELECT * FROM table1, table2" and I want to read values out of a ResultSet. What if the two tables have column names in common and I can't predict the column numbers? Is there any way to get table1.id and table2.id? rs.getString tells me "The column name table1.id not found." thanks much ben
On Wed, 15 Aug 2001 16:43:31 -0500, Ben Carterette wrote:
>I have a query like "SELECT * FROM table1, table2" and I want to read values
>out of a ResultSet. What if the two tables have column names in common and
>I can't predict the column numbers? Is there any way to get table1.id and
>table2.id? rs.getString tells me "The column name table1.id not found."
Does this also happen when you explicitly name the columns?
SELECT table1.id, ..., table2.id, ...
FROM table1, table2
Or if that doesn't help, try if a column label with the AS
clause works:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
http://www.postgresql.org/idocs/index.php?sql-select.html
SELECT table.id AS id1, ..., table2.id AS id2
FROM table1, table2
And then rs.getString("id1");
I think both solutions should work. Please let us know if they
don't.
Regards,
René Pijlman
This won't work because I don't know in advance of the SELECT which tables I'm going to be selecting from. The SELECT is done in a servlet that determines the tables based on request parameters. I tried "SELECT table1.*, table2.* FROM table1, table2", but it still can't tell the difference between columns with the same name. Thanks for your help ben On Wednesday, August 15, 2001, at 06:29 PM, Rene Pijlman wrote: > On Wed, 15 Aug 2001 16:43:31 -0500, Ben Carterette wrote: >> I have a query like "SELECT * FROM table1, table2" and I want to read >> values >> out of a ResultSet. What if the two tables have column names in >> common and >> I can't predict the column numbers? Is there any way to get table1.id >> and >> table2.id? rs.getString tells me "The column name table1.id not >> found." > > Does this also happen when you explicitly name the columns? > > SELECT table1.id, ..., table2.id, ... > FROM table1, table2 > > Or if that doesn't help, try if a column label with the AS > clause works: > > SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] > * | expression [ AS output_name ] [, ...] > http://www.postgresql.org/idocs/index.php?sql-select.html > > SELECT table.id AS id1, ..., table2.id AS id2 > FROM table1, table2 > > And then rs.getString("id1"); > > I think both solutions should work. Please let us know if they > don't. > > Regards, > René Pijlman >
On Thu, 16 Aug 2001 10:02:27 -0500, you wrote: >This won't work because I don't know in advance of the SELECT which >tables I'm going to be selecting from. I'm not sure if I understand this correctly. Whenever you write the SELECT statement you have to know the names of the tables, that's required by the syntax of select. What you're telling us is that you don't know the column names when you construct the statement, but you do know the column names 5 lines down when you want to do the rs.getString()? >On Wednesday, August 15, 2001, at 06:29 PM, Rene Pijlman wrote: >> On Wed, 15 Aug 2001 16:43:31 -0500, Ben Carterette wrote: >>> I have a query like "SELECT * FROM table1, table2" and I want to read >>> values >>> out of a ResultSet. What if the two tables have column names in >>> common and >>> I can't predict the column numbers? Is there any way to get table1.id >>> and >>> table2.id? rs.getString tells me "The column name table1.id not >>> found." >> >> Does this also happen when you explicitly name the columns? >> >> SELECT table1.id, ..., table2.id, ... >> FROM table1, table2 >> >> Or if that doesn't help, try if a column label with the AS >> clause works: >> >> SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] >> * | expression [ AS output_name ] [, ...] >> http://www.postgresql.org/idocs/index.php?sql-select.html >> >> SELECT table.id AS id1, ..., table2.id AS id2 >> FROM table1, table2 >> >> And then rs.getString("id1"); >> >> I think both solutions should work. Please let us know if they >> don't. >> >> Regards, >> René Pijlman >> > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster Regards, René Pijlman
The SELECT is in a servlet, something like this:
rs = stmt.executeQuery("SELECT * FROM " + request.getParameter("table1") +
", " + request.getParameter("table2"));
session.setAttribute("result", rs);
request.sendRedirect(request.getParameter("page2"));
that's a simplification of what it does, but it's good enough for
demonstration I think.
The .jsp file that calls the servlet knows what tables it wants to read out
of, and the .jsp file that the servlet redirects to knows what columns it
wants. The servlet doesn't know anything and hopefully shouldn't care. I
wanted to abstract it as much as possible.
sorry for the confusion and thanks for the help.
ben
-----Original Message-----
From: Rene Pijlman
To: Ben Carterette
Cc: pgsql-jdbc@postgresql.org
Sent: 8/20/01 12:28 PM
Subject: Re: [JDBC] select on multiple tables
On Thu, 16 Aug 2001 10:02:27 -0500, you wrote:
>This won't work because I don't know in advance of the SELECT which
>tables I'm going to be selecting from.
I'm not sure if I understand this correctly. Whenever you write
the SELECT statement you have to know the names of the tables,
that's required by the syntax of select.
What you're telling us is that you don't know the column names
when you construct the statement, but you do know the column
names 5 lines down when you want to do the rs.getString()?
>On Wednesday, August 15, 2001, at 06:29 PM, Rene Pijlman wrote:
>> On Wed, 15 Aug 2001 16:43:31 -0500, Ben Carterette wrote:
>>> I have a query like "SELECT * FROM table1, table2" and I want to
read
>>> values
>>> out of a ResultSet. What if the two tables have column names in
>>> common and
>>> I can't predict the column numbers? Is there any way to get
table1.id
>>> and
>>> table2.id? rs.getString tells me "The column name table1.id not
>>> found."
>>
>> Does this also happen when you explicitly name the columns?
>>
>> SELECT table1.id, ..., table2.id, ...
>> FROM table1, table2
>>
>> Or if that doesn't help, try if a column label with the AS
>> clause works:
>>
>> SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
>> * | expression [ AS output_name ] [, ...]
>> http://www.postgresql.org/idocs/index.php?sql-select.html
>>
>> SELECT table.id AS id1, ..., table2.id AS id2
>> FROM table1, table2
>>
>> And then rs.getString("id1");
>>
>> I think both solutions should work. Please let us know if they
>> don't.
>>
>> Regards,
>> René Pijlman
>>
>
>---------------------------(end of
broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
Regards,
René Pijlman
On Mon, 20 Aug 2001 12:50:22 -0500, you wrote:
>The SELECT is in a servlet, something like this:
>
>rs = stmt.executeQuery("SELECT * FROM " + request.getParameter("table1") +
>", " + request.getParameter("table2"));
>session.setAttribute("result", rs);
>request.sendRedirect(request.getParameter("page2"));
>
>that's a simplification of what it does, but it's good enough for
>demonstration I think.
>
>The .jsp file that calls the servlet knows what tables it wants to read out
>of, and the .jsp file that the servlet redirects to knows what columns it
>wants. The servlet doesn't know anything and hopefully shouldn't care. I
>wanted to abstract it as much as possible.
How about using ResultSetMetaData.getColumnName() on the
ResultSet to dynamically query the column names? See:
http://java.sun.com/j2se/1.3/docs/api/java/sql/ResultSetMetaData.html
I don't know that this will (and should) return when you do
SELECT * from A, B and there is a column c in both tables.
Hopefully it returns A.c and B.c.
Regards,
René Pijlman
Unfortunately id doesn't return A.c and B.c. Try it in psql...
One way to solve this is to fix the backend to return fully qualified
column names.
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Rene Pijlman
Sent: August 20, 2001 3:49 PM
To: Carterette, Ben
Cc: 'pgsql-jdbc@postgresql.org'
Subject: Re: [JDBC] select on multiple tables
On Mon, 20 Aug 2001 12:50:22 -0500, you wrote:
>The SELECT is in a servlet, something like this:
>
>rs = stmt.executeQuery("SELECT * FROM " +
>request.getParameter("table1") + ", " +
>request.getParameter("table2")); session.setAttribute("result", rs);
>request.sendRedirect(request.getParameter("page2"));
>
>that's a simplification of what it does, but it's good enough for
>demonstration I think.
>
>The .jsp file that calls the servlet knows what tables it wants to read
>out of, and the .jsp file that the servlet redirects to knows what
>columns it wants. The servlet doesn't know anything and hopefully
>shouldn't care. I wanted to abstract it as much as possible.
How about using ResultSetMetaData.getColumnName() on the ResultSet to
dynamically query the column names? See:
http://java.sun.com/j2se/1.3/docs/api/java/sql/ResultSetMetaData.html
I don't know that this will (and should) return when you do SELECT *
from A, B and there is a column c in both tables. Hopefully it returns
A.c and B.c.
Regards,
René Pijlman
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Dave Cramer writes: > Unfortunately id doesn't return A.c and B.c. Try it in psql... > > One way to solve this is to fix the backend to return fully qualified > column names. The backend is doing the right thing. In the most general case of a complex join you don't even know what table a column came from. If you have ambiguous names you should be using AS clauses or JOIN syntax. SQL actually requires to raise an error if you have duplicate output column names, IIRC. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter