Help with a query

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Alexander Cohen

    Help with a query

    Hi,

    Im trying to get a list of all groups that a certain user is a member
    of. Can anyone help me with the sql to get that?

    thanks!


    --
    Alexander Cohen

    (819) 348-9237
    (819) 432-3443


    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to majordomo@postg resql.org so that your
    message can get through to the mailing list cleanly

  • Erwin Van de Velde

    #2
    Re: Help with a query

    Hi,

    Without further information, this is rather difficult, but assuming that you
    have a table with userdata with a primary key UID and a unique UserName, and
    that you have a table of usergroups with a primary key GID and unique
    GroupName, the best way is to make a third table Users_Usergroup s e.g.
    where you put the couples (UIDX, GIDX) for a user with UID=UIDX and who is in
    group GIDX.
    The query would then be:
    SELECT GroupName FROM Users_Usergroup s JOIN Users ON Users.UID =
    Users_Usergroup s.UID JOIN Usergroups ON Usergroups.GID = Users_Usergroup s.GID
    WHERE UserName Like 'The User';

    Greetings,
    Erwin Van de Velde
    Student of University of Antwerp
    Belgium


    On Thursday 26 February 2004 22:47, Alexander Cohen wrote:[color=blue]
    > Hi,
    >
    > Im trying to get a list of all groups that a certain user is a member
    > of. Can anyone help me with the sql to get that?
    >
    > thanks![/color]


    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to majordomo@postg resql.org so that your
    message can get through to the mailing list cleanly

    Comment

    • Klint Gore

      #3
      Re: Help with a query

      On Thu, 26 Feb 2004 16:47:10 -0500, Alexander Cohen <alex@toomuchsp ace.com> wrote:[color=blue]
      > Im trying to get a list of all groups that a certain user is a member
      > of. Can anyone help me with the sql to get that?[/color]

      select groname[color=blue]
      >from pg_group[/color]
      where (select usesyside from pg_shadow where usename = 'postgres') =
      any(grolist);

      klint.

      +---------------------------------------+-----------------+
      : Klint Gore : "Non rhyming :
      : EMail : kg@kgb.une.edu. au : slang - the :
      : Snail : A.B.R.I. : possibilities :
      : Mail University of New England : are useless" :
      : Armidale NSW 2351 Australia : L.J.J. :
      : Fax : +61 2 6772 5376 : :
      +---------------------------------------+-----------------+

      ---------------------------(end of broadcast)---------------------------
      TIP 6: Have you searched our list archives?



      Comment

      Working...