CODEWITHSUNDEEP
mysqlsql
Java Questions
Java Questions

Reputation: 7953

need assistance on constructing sql query

i have three tables like follows:

this one table : m_application_resources

 resourceid resource_name   menu_group_id   menu_name   creation_date   last_created_by updation_date   last_updated_by

and the second table : m_roles

roleid  rolename    description creation_date   last_created_by updation_date   last_updated_by

and the third table is : m_access_matrix

accessid    resourceid  roleid  creation_date   last_created_by updation_date   last_updated_by

Relationship for the table is : resourceid and roleid

and this my query 

select  am.accessid, ar.resource_name,rls.rolename
from m_application_resources ar, m_roles rls,m_access_matrix am 
where ar.resourceid=am.resourceid

this returns the following :

ccessid resource_name   rolename
1   DepartmentAction    Admin
1   DepartmentAction    Client
1   DepartmentAction    Doctors
2   PositionsAction     Admin
2   PositionsAction     Client
2   PositionsAction     Doctor

the result is wrong and i don't know how to go about.

Ex : DepartmentAction should come only once and the role name should be any one.

SqlfiddleFiddle

Please help

Upvotes: 1

Views: 53

Answers (2)

mjoshawa
mjoshawa

Reputation: 115

It would be best to explicitly join the tables in your query. I'm not 100% sure I understand your tables, but this should work:

SELECT am.accessid, ar.resource_name, rls.rolename
FROM (m_application_resources ar INNER JOIN m_access_matrix am 
ON ar.resourceid = am.resourceid) INNER JOIN mroles rls ON am.roleid = rls.roleid

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726539

You do not have a condition on role_id:

select  am.accessid, ar.resource_name,rls.rolename
from m_application_resources ar, m_roles rls,m_access_matrix am 
where ar.resourceid=am.resourceid AND am.roleid = rls.roleid

I would convert the query to ANSI SQL syntax for joins for better clarity:

SELECT am.accessid, ar.resource_name,rls.rolename
FROM m_application_resources ar
JOIN m_access_matrix am ON ar.resourceid=am.resourceid
JOIN m_roles rls ON am.roleid = rls.roleid

Upvotes: 3

Related Questions