SQL Help with 3 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Help with 3 tables

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-06-08 : 08:36:54

I have 3 tables that mange roles and links. Currently there are a total of 3 roles and a total of 10 links.
I'm trying to show each role with all 10 links under it and only the links that go with that role are checked. This way you can
determine which links go with each role. Make sense?

Here is the table structure.

Roles
-RoleID
-Sort

UserLinks
-LinkID
-Link

RolesUserLinks
-RoleID
-LinkID

SELECT DISTINCT SA_UserLinks.DisplayText, SA_Roles.Role
FROM SA_Roles
INNER JOIN SA_RolesUserLinks ON SA_Roles.RoleID = SA_RolesUserLinks.RoleID
CROSS JOIN SA_UserLinks
ORDER BY SA_UserLinks.DisplayText

Now every role shows all the links. So there are 3 roles and 10 links each.
Now I just need to determine if that role gets that link

I'm trying this...but it returns 2 links for each for each of the 10 roles...

SELECT DISTINCT SA_UserLinks.DisplayText, SA_Roles.Role, CASE WHEN SA_RolesUserLinks.LinkID = SA_UserLinks.LinkID THEN 1 ELSE 0 END AS Checked
FROM SA_Roles INNER JOIN
SA_RolesUserLinks ON SA_Roles.RoleID = SA_RolesUserLinks.RoleID CROSS JOIN
SA_UserLinks
ORDER BY SA_UserLinks.DisplayText

DisplayText Role Checked
Pricing Customer 1
Pricing Customer 0
Pricing Rep 1
Pricing Rep 0

jhermiz

3564 Posts

Posted - 2005-06-08 : 08:39:41

It's returning two because they are not distinct. You are assuming distinct works on a field but it does not it works on the entire row. To fix the issue you will need to first understand what you really want. Then you will need to use a GROUP BY but in order to get the single return rather than multiple returns you will need to use Aggregate functions like MAX(), FIRST(), MIN(), etc. So drop the distinct and play around with some aggregates and the GROUP BY.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources