Hello all,

This is my first time on the forums, so please bear with me.  Im also a somewhat novice SQL coder but I have a question that I feel might be best answered my the more advanced folks on this forum.

I have 3 tables, a user table, a group table and a membership table.

Say:

user_table
+-------------------+
|   user_PK         |
|   Id              |
|   Name            |
|   Description     |
+-------------------+

Group_table
+-------------------+
|  group_PK         |
|  Id               |
|  Name             |
|  Description      |
+-------------------+

Group_members
+----------------------+
|  group_members_PK    |
|  Group_ID            |
|  Member_ID           |
|  Member_type         |
+----------------------+

The group_ID is linked to the Group_table.ID and the Member_ID can be either linked back to the group_table or user_table depending on the value of member_type.  

It is possible to have nested groups, and each level of nesting can have users as well not just nested groups.  Sort of how the windows group nesting model works.

I would like to end up with being able to have output that lists only users, their descriptions and their nested group path if any.

If you need more clarification please let me know.   I know this maybe a simple thing, but Im unsure how to do it.  Thanks in advance.

You can. Are you familiar with the term "nested sets"?

They are extremely useful for accomplishing what you're trying to do. I'd go into more detail, but odds are Celko will spot this thread and jump in. He literally wrote the book on nested sets and is a regular around here.

In the mean time, here are a few links to get you started...

http://en.wikipedia.org/wiki/Nested_set_model

http://www.sqlservercentral.com/articles/Hierarchy/94040/

and just because it's one of my own old threads... https://social.msdn.microsoft.com/Forums/sqlserver/en-US/83eaa7a4-f2d6-4d69-b47a-f74b1b29598e/a-practical-approach-the-nested-sets-when-creating-hierarchies?forum=transactsql

 

A recursive CTE between group and group_member sounds the way to go to me. Cycle through so that each member has only one row and then join on to the user table.

Either concatenate the group descriptions to one column or you will need to decide on a finite number of columns that you will delve to and a pivot can help push them to columns.

>> This is my first time on the forums, so please bear with me.  I am also a somewhat novice SQL coder but I have a question that I feel might be best answered my the more advanced folks on this forum. <<

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. There is no generic id, name, etc. in RDBMS. Tables have to have a key, but we do not put _pk on the name. Read a copy of https://www.simple-talk.com/books/sql-books/119-sql-code-smells/ so you will stop tibbling and other bad practices.  

You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. 

Avoid dialect in favor of ANSI/ISO Standard SQL. And do not draw ASCII pictures! They do not compile. 

>>The group_id is linked [sic] to the group_table.id [sic] And the member_id can be either linked [sic] back to the group_table or user_table depending on the value of member_type.  <<

RDBMS has no links; that was in network databases in the 1970's. No wonder you want to use recursive CTEs; they are cursors 

>> It is possible to have nested groups, and each level of nesting can have users as well not just nested groups.  Sort Of how the windows group nesting model works. <<

Then you should use a nested sets model.  Since you did not post sample data, DDL  or correct specs, Google it.