what will be sql query for this

Question

i want to Find out role.id by searching role.name and then retrieve all users by matching their user_id = founded role.id

select * from user join Role on Role.id=user.role where role.name='provider'; — JYoThI, Feb 03 '17 at 04:14
When composing a good Stack Overflow question do try and post the result of `SHOW CREATE TABLE` and not screenshots that often contain nothing but irrelevant cutter like action links we can't click. — tadman, Feb 03 '17 at 05:43

score 1 · Answer 1 · edited Feb 03 '17 at 04:47

1

Below query will work:

select id,user_id,username from (select id from Role where name = 'provider') as t
join
user u on t.id = u.role

edited Feb 03 '17 at 04:47

Nisse Engström

answered Feb 03 '17 at 04:14

Chintan Udeshi

you have used sub-query but i was trying using join only i got it. – Mukesh Kumar Feb 03 '17 at 05:48

score 0 · Answer 2 · answered Feb 03 '17 at 05:49

0

SELECT * FROM users INNER JOIN role on user.role  = role.id WHERE role.name = 'provider'

answered Feb 03 '17 at 05:49

Mukesh Kumar

1

When giving an answer it is preferable to give [some explanation as to WHY your answer](http://stackoverflow.com/help/how-to-answer) is the one. – Stephen Rauch Feb 03 '17 at 16:43

2 Answers2