need assistance on constructing sql query

Question

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

Sergey Kalinichenko · Accepted Answer · 2012-11-07 18:23:29Z

3

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

answered Nov 7, 2012 at 18:23

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nick Vaccaro Over a year ago

Great point converting to ANSI syntax. Please do take that advice and use proper formatting in your code.

Java Questions Over a year ago

the query gives empty result set but i have some values

Sergey Kalinichenko Over a year ago

@Anto Very interesting... Could you create a "sandbox" on www.sqlfiddle.com with the sample data, and post a link?

Java Questions Over a year ago

thanks i shall give you the tables, Please have a look at update

Andriy M Over a year ago

@Anto: Please take a closer look at the example you've posted. All m_access_matrix rows reference the roleid of 1, but m_roles doesn't have a row by that roleid.

|

mjoshawa · Accepted Answer · 2012-11-07 18:29:49Z

2

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

answered Nov 7, 2012 at 18:29

mjoshawa

1158 bronze badges

1 Comment

Java Questions Over a year ago

the query gives empty result set but i have some values

Collectives™ on Stack Overflow

need assistance on constructing sql query

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related