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