Trusted Contributor.. beckie Trusted Contributor..
Trusted Contributor..
1934 views

SQL query : list of users in a domain ?

Jump to solution

Dear all,

 

I would like to extract from DB with a SQL query the list of users belonging to projects in 1 domain.

But I'm a little bit new in SQL and inner join usage.

Could you help me ?

I will prefer sql than script becasue I have a access on DB DB with Sql developper for select query.

 

Thanks by advance.

beckie

Tags (1)
0 Likes
1 Solution

Accepted Solutions
Highlighted
Trusted Contributor.. beckie Trusted Contributor..
Trusted Contributor..

Re: SQL query : list of users in a domain ?

Jump to solution

Hi,

Yes it helps,

I finally succeed in writing the sql statement :

 

select distinct full_name from QCSITEADMIN_DB.users table_U INNER JOIN QCSITEADMIN_DB.users_projects table_UP on table_U.user_id= table_UP.user_id INNER JOIN QCSITEADMIN_DB.projects table_P on table_UP.project_id = table_P.project_id where table_P.domain_name='XXXX';

 

I hope it will help someone in upcoming weeks or months 🙂

Thanks a lot.

Best regards

Beckie

0 Likes
3 Replies
SahilS Absent Member.
Absent Member.

Re: SQL query : list of users in a domain ?

Jump to solution

Try Quering USERS table in the site admin database (qcsiteadmin_db).

Hope this will help.

Regards
0 Likes
Absent Member.. Trudy Claspill Absent Member..
Absent Member..

Re: SQL query : list of users in a domain ?

Jump to solution

Here are three references to help you understand SQL JOINs.

 

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

 

http://stackoverflow.com/questions/3308122/how-do-i-decide-when-to-use-right-joins-left-joins-or-inner-joins-or-how-to-dete/3308153#3308153

 

http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join

 

To get the data you will have to look at three tables:

USERS_PROJECTS contains user id numbers and project id numbers

USERS contains the user id numbers linked to the rest of a user's info, like login name and full name

PROJECTS contains the project id numbers and other general project info, like the project name and the Domain (id number and name) that the project is in.

 

[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
Highlighted
Trusted Contributor.. beckie Trusted Contributor..
Trusted Contributor..

Re: SQL query : list of users in a domain ?

Jump to solution

Hi,

Yes it helps,

I finally succeed in writing the sql statement :

 

select distinct full_name from QCSITEADMIN_DB.users table_U INNER JOIN QCSITEADMIN_DB.users_projects table_UP on table_U.user_id= table_UP.user_id INNER JOIN QCSITEADMIN_DB.projects table_P on table_UP.project_id = table_P.project_id where table_P.domain_name='XXXX';

 

I hope it will help someone in upcoming weeks or months 🙂

Thanks a lot.

Best regards

Beckie

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.