Re: Retrieving Columns by table-alias

Dominik Bruhn wrote:
> Assume the following Tables:
>
> post:
> postid: serial
> postuserid: integer
> edituserid: integer
>
> user:
> userid: serial
> username: varchar
>
> Every "post" got two Users accociated to them, the onw who wrote it at
> first time (postuserid) and the last user who edited it
> (edituserid). So
> get a list of all post I use the following query:
>
> SELECT * FROM post
> LEFT JOIN user AS postuser ON (post.postuserid=postuser.userid)
> LEFT JOIN user AS edituser ON (post.edituserid=edituser.userid)
>
>
> This works fine until I want to retrieve the columns in JDBC:
> As "userid" is not a unique label (could be postuser.userid and
> edituser.userid) I need another solution.
>
> When using mysql I could write:
> ResultSet.getString("postuser.username") for example, so prefix the
> column with the table-alias.
>
> This syntax doesnt seem to be avaliable.
>
> Is there another solution?
>
> The only thing I could think of was to name all columns in the query
> like this:
>
> SELECT
> post.*,
> postuser.userid AS postuser_userid,
> postuser.username AS postuser_username,
> edituser.userid AS edituser_userid,
> edituser.username AS edituser_username
> FROM post
> LEFT JOIN user AS postuser ON (post.postuserid=postuser.userid)
> LEFT JOIN user AS edituser ON (post.edituserid=edituser.userid)
>
> BUT: This solution isnt what I want: Everytime I add another column to
> the user-table, I have to rewrite ALL Queries in Order to retrieve the
> data. I simply want it to access in the Result-Set.

I thought about it, and I don't think there is a way.

I'm a little bit surprised why you need that though.

The only reason you give for not using aliases is that you want to keep
your 'SELECT *' so you don't have to change the query when you add a new
field.

Now there are two cases:

a) you want to retrieve a newly added field with ResultSet.getString()
b) you don't.

In case b) you obviously wouldn't have to change the SELECT list because
you
do not need to select the new fields at all.

In case a) you'll have to edit your code anyway to add the retrieval and
handling of the new field.
In comparison to this the task of adding the field to the SELECT list as
well seems no big additional trouble (to me).

Yours,
Laurenz Albe