sql - Use a join or a subquery to select a single column -

i'm writing app has chat function , using postgres db. there 4 tables involved- users (holds user data, including id), groups (holds list of groups, including groupid), group_users (holds mapping of groupids userids, 1 many), , groupchats (holds mapping of groupid userid entered chat , text itself). tables have obvious foreign keys between them.

i want rows in groupchats belong given groupid. wish if person requesting in group. want name of person talking each row (remember row stores user ids).

the following i've come with. i'm curious if there's better way? i'm using subquery name of user in current row. should using join instead? also, believe second subquery (for exists clause) should run once, correct in that? or better off in client side logic (making 2nd round trip db)? expext db can optimize this.

select userid, chat, time,        (select firstname users id=groupchat.userid)  groupchat  groupid=$1    , exists (select userid                group_users                groupid=$1 , userid=$2)  order time asc; 

your method fine. can write explicit join. using correlation means have include $1 once in query:

select gc.userid, gc.chat, gc.time, u.firstname groupchat gc join      users u      on u.id = gc.userid gc.groupid = $1 ,       exists (select 1                group_users fu               gu.groupid = gc.groupid , gu.userid = $2              )  order gc.time asc; 

if have large amount of data, want following indexes: groupchat(groupid, time), users(id, firstname), , group_users(groupid, userid).