SELECT U.FIRSTNAME, U.LASTNAME, U.ADDRESS, <more user fields>, UG.GROUPNAME
FROM USER U
LEFT JOIN USERRELATIONS UR ON U.USERID = UR.USERID
LEFT JOIN USERGROUP UG ON UR.GROUPID = UG.GROUPID
WHERE U.FIRSTNAME = 'RAMBABU' AND U.LASTNAME = 'PIRIDI'

This will get you one record if you are a member of none or one
usergroup, and lots of records if you are a member of lots of groups.

If you wanted to show only the name of the group if it was certain
particular groups, you could do something like:

SELECT U.FIRSTNAME, U.LASTNAME, U.ADDRESS, <more user fields>, UG.GROUPNAME
FROM USER U
LEFT JOIN USERRELATIONS UR ON U.USERID = UR.USERID
LEFT JOIN USERGROUP UG ON UR.GROUPID = UG.GROUPID
AND UG.GROUPTYPE = 'FIREBIRD'
WHERE U.FIRSTNAME = 'RAMBABU' AND U.LASTNAME = 'PIRIDI'

I.e. don't use anything from the right side of a LEFT JOIN in your WHERE
clause (since this changes it to an inner join), keep it in the JOIN
clause. With normal [INNER] JOINs as well as the left side of a LEFT
JOIN, you may put things in the WHERE clause.

HTH,
Set

rambabu.piridi@... wrote:

> Hi,
>
> I have three tables named User, UserGroup, UserRelations.
> User table contains informatrion about the user like userId, userName,
> email,..
> UserGroup table contains information about all the groups like groupId,
> groupName,..
> UserRelations table contains information about groupId, ele_id(UserId
> from User table, only if the user is in a particular group).
> User table also contains information of other users who are not part of
> the UserGroup. And one user can be part of any number of groups.
>
> Now my requirement is to fetch all the columns from user table + His
> group name from UserGroup (if he belongs to a particular group,
> otherwise empty).
>
> Can any one specify the query to get the required details.
>
>
> Regards,
> Rambabu