Retrieving Columns by table-alias

[Dominik Bruhn]

Hy,
I used MySQL for years and now wanted to try out something new (and
perhaps better).

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.

Can anybody give me a hint?

Thanks in advance
Dominik Bruhn

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

[Albe Laurenz]

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match