Search code examples
mysqlsqldatabaseinner-join

How to retrieve data with multiple join in my sql?

I have following table in my SQL database

user_

userId | userName

organization_

org_id | parent_org_id_ | name

role_

roleId | roleName

user_roles

userId | roleId

users_organization

userId |  organizationId

...Now what i want is from org_Id= ? and role_name = ? I want to retrieve the name of the user from user_ table related to that organization with the specific role name.so can anyone please tell me.. how to do it?

Solution

  • Try ::

    Select *
from
user_ u
INNER JOIN user_roles ur on (u.userId = ur.userId)
INNER JOIN role_ r on (ur.roleId = r.roleId)

INNER JOIN users_organization uo (u.userId = uo.userId)
INNER JOIN organization_ o on (uo.organizationId= o.orgId)
where uo.org_id =? and  r.role_name=?