How do you add a relationship in a dataset that contains three tables : e.g. users, roles and user_roles

Question

My problem is like this : i got 3 tables :

- users (user_id, name)
- roles (role_id, role_name)
- user_roles(user_id, role_id);

I load them all in a dataset The challenge is to build a relation (or more?) in this dataset, that when given to a ultragrid (infragistics) they would display right - for each user his roles (name, description) thanks!

Answer 1

I am not sure what you mean by "name, description", since there is no description column in your schema, but here is a guess at what you want:

select u.user_id, u.name as UserName, r.role_id, r.role_name
from users u
inner join user_roles ur on u.user_id = ur.user_id
inner join roles r on ur.role_id = r.role_id