How list all USERs FROM a especific GROUP USING SQL?

[Sardenberg]

I want to list, using sql editor, all users name from a specific group.
Reading documentation, I only learned how to show the groups or the users, using simples filters, like:

SHOW GROUPS LIKE '*XPTO*';

SHOW GROUPS WITH USER `test@gmail.com`

SHOW USERS LIKE '*gus*'

I would like to create something like:
select users_name from USERS where group_name = "XPTO_GROUP";
or
SHOW USERS WHERE GROUP "xpto_group";

[Kaniz_Fatma]

Hi @Sardenberg,  To retrieve a list of users from a specific group using SQL, you can follow these steps:

Assumptions:

• Let’s assume you have three tables: USERS, GROUPS, and GROUP_USERS.
• The USERS table contains user information.
• The GROUPS table contains group information.
• The GROUP_USERS table establishes the relationship between users and groups.

SQL Query: You can use the following query to get a list of users who belong to a specific group and include all the groups they are members of:

SELECT u.*, GROUP_CONCAT(g.name) AS groups FROM USERS u JOIN GROUP_USERS gu ON gu.USER_ID = u.ID JOIN GROUPS g ON g.ID = gu.GROUP_ID WHERE g.name = 'XPTO_GROUP' GROUP BY u.ID;

Explanation:

• We join the USERS, GROUP_USERS, and GROUPS tables based on their respective IDs.
• The GROUP_CONCAT(g.name) function aggregates the group names for each user.
• The WHERE clause filters the results to include only users from the specified group.
• The GROUP BY ensures that each user appears only once in the result set.

Customization:

• Replace 'XPTO_GROUP' with the actual name of the group you want to query.

This query will provide you with a list of users from the specified group along with all the other groups they belong to. 📊👥

[Sardenberg]

This solution seems to have been answered by an AI.
I need to know how create a SELECT for create this tables: USERS, GROUPS and GROUP_USERS.

[arch_db]

---

@Sardenberg wrote:

This solution seems to have been answered by an AI.
I need to know how create a SELECT for create this tables: USERS, GROUPS and GROUP_USERS.

---

# Databricks notebook source
from pyspark.sql.functions import lit

# COMMAND ----------

df_groups = spark.sql("""SHOW GROUPS""")

# COMMAND ----------

df_users = spark.sql("""SHOW USERS""")

# COMMAND ----------


result = spark.createDataFrame([], "name: string, directGroup: boolean, user: string")
for user in df_users.collect():
    df_user_groups = spark.sql(f"""SHOW GROUPS WITH USER `{user.name}`""").withColumn("user", lit(user.name))
    result = result.unionAll(df_user_groups)

# COMMAND ----------

display(result) # Here you can create temp view and select data

It will work fine if groups added to workspace.