1 Reply Latest reply on Oct 20, 2015 9:36 PM by Derek Lawson

    sql query to list users from a group (& multiple groups) plus specified user

    Paul Philpott
    Paul Philpott Oct 15, 2015 3:13 PM

      In EPDM list using SQL query, I want to display all the users in a particular group, with the capacity to designate all users from multiple groups.

      Oh, And I'd also like the resulting list to include one or more values that I specify, which are not userids in any group, such as "TBD" or "NA"

       

      Thanks!

      • 970 Views
      • Categories: Enterprise PDM
      • Tags:
        • Re: sql query to list users from a group (& multiple groups) plus specified user
          Derek Lawson
          Derek Lawson Oct 20, 2015 9:36 PM (in response to Paul Philpott)

          Paul,

           

               I wrote something like that for another customer. The query below should get you started.

           

          select '' as Fullname --This is your additional option. A blank value in this case, but could be 'N/A'

          union

          select distinct U.FullName

          from Groups G, Users U, GroupMembers GM

          where  G.Groupname = '' -- Enter group name here between the single quotes.

          and G.GroupID = GM.GroupID

          and U.UserID = GM.UserID

          and U.Enabled = 1

          --For mulitple groups, add another union command here and then re-use the group member syntax from above and search for a different group name.

          order by Fullname

           

          Derek M. Lawson