SQL Statement Problem Missing Data

[dcharnigo]

Here is my database layout:

Institution:
...inst_id
...inst_name

IsUser:
...user_id
...login
...Institution_inst_id

Membership:
...IsUser_user_id
...Role_role_id

Role:
...role_id
...Institution_inst_id
...role_name

The Database is the back end for an application. When a user is created they are NOT assigned a role. But this can be done later. I need to return all users and associated information from the other tables. I can do this for all users in a role using the membership table, the problem is the users that are not assigned a role are not in the membership table and I have nothing to join on. any ideas? here is my working SQL that returns users in a role:

("SELECT i.user_id, i.login, r.role_name, r.role_id, t.inst_name, t.inst_id from IsUser i, Role r, Membership m, Institution t where i.user_id = m.IsUser_user_id and r.role_id = m.Role_role_id ORDER BY t.inst_name, r.role_name")

I have a feeling I may have to Use two different SQL statements, but I would like to do the job in one.

Also IsUser.Institution_inst_id --> Role.Institution_inst_id --> Institution.inst_id

Thanks for the help.

[ronverdonk]

Welcome to The Scripts!

Use the LEFT JOIN. That way it will display all users and the missing values will be replaced by NULLS.

Look at this thread to see a similar problem http://www.thescripts.com/forum/thread599419.html

Ronald :cool:

[dcharnigo]

Welcome to The Scripts!

Use the LEFT JOIN. That way it will display all users and the missing values will be replaced by NULLS.

Look at this thread to see a similar problem http://www.thescripts.com/forum/thread599419.html

Ronald :cool:

Thanks for the help her is my end result:

SELECT i.user_id, i.login, i.Institution_inst_id, t.inst_name, m.Role_role_id, r.role_name
FROM IsUser AS i
LEFT JOIN Institution AS t ON (i.Institution_inst_id=t.inst_id)
LEFT JOIN Membership AS m ON (i.user_id=m.IsUser_user_id)
LEFT JOIN Role AS r ON (m.Role_role_id=r.role_id)