Joining 4 tables issue

[Marclem]

Greetings,

I have below query which I am trying to get the last table named st_role column st_role.sf_name data but I am getting "null" values...

Basically sf_role_id PK is also at table st_useraccount_roles and tables st_user and st_useracount_roles have a matching PK named sf_user_id.

SELECT
st_user.sf_firstname as "First Name", st_user.sf_lastname as "Last Name", st_user.sf_emailaddress as "Email",
st_user.sf_username as "Username", date(st_user.sf_last_login) as "Last Login",
st_department.sf_name as "Workgroup",
(CASE WHEN st_user.sf_active = '1'::boolean THEN 'Enabled' WHEN st_user.sf_active = '0'::boolean THEN 'Disabled' END) as "Account Status",
st_role.sf_name
FROM
st_user
JOIN st_department on (st_user.sf_department_id = st_department.sf_id)
LEFT JOIN st_useraccount_roles on (st_user.sf_user_id = st_useraccount_roles.sf_user_id)
LEFT JOIN st_role on (st_useraccount_roles.sf_role_id = st_role.sf_role_id)
ORDER BY st_user.sf_firstname ASC

Thank you for your support!

 

[ianbtheog]

Hello,

Whilst I am not sure I can assist or not based on the information, you say that these tables have matching primary keys defined, do these tables have foreign key constraints?

It's possible that some tables don't have matching keys I would verify individually that each key is present in each table, the LEFT JOINs are a likely place for this to occur so I'd check that the keys actually match the joined table doesn't have a corresponding match you'll get a null value.

Do all rows return null in the st_role.sf_name column or only a subset?

Regards,
Ian

 

[Marclem]

Hello,

Whilst I am not sure I can assist or not based on the information, you say that these tables have matching primary keys defined, do these tables have foreign key constraints?

It's possible that some tables don't have matching keys I would verify individually that each key is present in each table, the LEFT JOINs are a likely place for this to occur so I'd check that the keys actually match the joined table doesn't have a corresponding match you'll get a null value.

Do all rows return null in the st_role.sf_name column or only a subset?

Regards,
Ian

Thank your for your reply Ian,

I am not quite sure if these tables have foreign key constraints. Basically the first 3 tables I can gather all data I am requesting, then when I try to gather data with the 4th table that's where the problem occurs. Below all 4 tables columns and and the id fields I joined them together for reference:

 

[Andrzejek]

With your setup, I would assume every User belongs to just one Department, and can have at least one role, but user can be assigned more than one role, hence the st_useraccount_roles table.

If that's true, I would try:
SELECT
U.sf_firstname as "First Name", U.sf_lastname as "Last Name", U.sf_emailaddress as "Email",
U.sf_username as "Username", date(U.sf_last_login) as "Last Login", D.sf_name as "Workgroup",
(CASE WHEN U.sf_active = '1' THEN 'Enabled' ELSE 'Disabled' END) as "Account Status",
R.sf_name
FROM st_user U
JOIN st_department D on (U.sf_department_id = D.sf_id)
JOIN st_useraccount_roles UR on (U.sf_user_id = UR.sf_user_id)
JOIN st_role R on (UR.sf_role_id = R.sf_role_id)
ORDER BY U.sf_firstname ASC

 

[Marclem]

With your setup, I would assume every User belongs to just one Department, and can have at least one role, but user can be assigned more than one role, hence the st_useraccount_roles table.

If that's true, I would try:
SELECT
U.sf_firstname as "First Name", U.sf_lastname as "Last Name", U.sf_emailaddress as "Email",
U.sf_username as "Username", date(U.sf_last_login) as "Last Login", D.sf_name as "Workgroup",
(CASE WHEN U.sf_active = '1' THEN 'Enabled' ELSE 'Disabled' END) as "Account Status",
R.sf_name
FROM st_user U
JOIN st_department D on (U.sf_department_id = D.sf_id)
JOIN st_useraccount_roles UR on (U.sf_user_id = UR.sf_user_id)
JOIN st_role R on (UR.sf_role_id = R.sf_role_id)
ORDER BY U.sf_firstname ASC

Thank you Andy,

I ran your query and got 0 results, but if do and left join I get data but again the r.sf_name shows "null" values.

 

[Andrzejek]

Add to the end of your Select:
Select ...,
R.sf_name, R.df_role_id
From...
and see if you get id's for these NULLS.
Is it possible you have some Roles without their names in st_role table?

 

[Marclem]

Add to the end of your Select:
Select ...,
R.sf_name, R.df_role_id
From...
and see if you get id's for these NULLS.
Is it possible you have some Roles without their names in st_role table?

Adding r.sf_role_id to the end of the Select statement still got me "null" values. Basically as shown below tables st_useraccount_roles and st_role have a matching PK named sf_role_id but from these two tables only table st_useraccount_roles have matching PK key from table st_user, so basically I need the data from table st_role;s column named sf_name to show up but I am getting null values.

 

[Andrzejek]

I cannot see your data in your tables, so it is difficult to know what's going on.
It is possible you have Users without any Role(s)?

To check, run this statement:
Select sf_user_id
From sf_user
Where sf_user_id NOT IN
(Select sf_user_id
From st_useraccount_roles)

 

[Marclem]

I cannot see your data in your tables, so it is difficult to know what's going on.
It is possible you have Users without any Role(s)?

To check, run this statement:
Select sf_user_id
From sf_user
Where sf_user_id NOT IN
(Select sf_user_id
From st_useraccount_roles)

Attached the results from your query:

Select sf_user_id
From st_user
Where sf_user_id NOT IN
(Select sf_user_id
From st_useraccount_roles)

 

[Andrzejek]

It looks like you have 941 Users with no Role(s) assigned to them. And that's why their st_role.sf_name is NULL
Their IDs are not in st_useraccount_roles table

 

[Marclem]

It looks like you have 941 Users with no Role(s) assigned to them. And that's why their st_role.sf_name is NULL
Their IDs are not in st_useraccount_roles table

I see... Trying to understand which table I can get the user roles from to show up as shown from the system's front-end:


As you can see below from the st.role table is where under the sf_name column it shows the user roles as above on the front-end system:

 

[Andrzejek]

If the User does not have any Role assigned, there is nothing to display in (role's) Name column for that User
(unless you want to display: 'Role not assigned' or something like that)

And I see kind of poor data in your st_role table:
800 Super Administrator
...
811 Super Administrator
...
916 Super Administrator