mysql - Select all rows from two tabels -

i new database design , working on school project. have 3 tables (users, roles , user_roles). have created users , roles available users. have created user_roles table contains users , roles have been assigned them.

i need query give me users, roles available , if allocated users. aim list available users , available roles can assign or remove rights access.

how achieve this?

the following schema of tables

users  (   `user_id` int unsigned not null auto_increment ,   `username` varchar(45) null ,   `password` varchar(45) null ,   `first_name` varchar(45) null ,   `last_name` varchar(45) null ,   `status` varchar(45) not null default 'active' ,   `date_from` date null ,   `date_to` date null ,   `created_by` varchar(45) not null default 'sysadmin' ,   `create_date` timestamp not null default now() ,  primary key (`user_id`)  roles tables `role_id` int unsigned not null auto_increment ,   `role_name` varchar(45) not null ,   `date_from` datetime null default '2000-01-01' ,   `date_to` datetime null default '2999-12-31' ,   `created_by` varchar(45) not null default 'sysadmin' ,   `create_date` timestamp not null default now() ,   primary key (`role_id`)   user_role table `user_role_id` int unsigned not null auto_increment ,   `user_id` int unsigned not null ,   `role_id` int unsigned not null ,   primary key (`user_role_id`) ,   constraint `fk_user_roles_users1`     foreign key (`user_id` )     references `users` (`user_id` )     on delete cascade     on update cascade,   constraint `fk_user_roles_roles1`     foreign key (`role_id` )     references `roles` (`role_id` )     on delete cascade     on update cascade 

my aim select users table users , show roles available allocated , if allocated

for example

user_id      role_id      role_allocated  

something similar

use sql join on condition follows:

select * user_roles ur  inner join users u on ur.user_id = u.user_id inner join roles r on ur.role_id = r.role_id; 

assuming primary key tables : users => user_id, roles => role_id. query return users , it's roles assigned in user_roles table. have been easier answer question if had provided keys , other attributes of tables.