Tying together 5 tables to retrieve data - a bit long, but very clear 1

Ovatvvon · Aug 31, 2005

This is going to seem like a lot here, but you don't need to read all of it, I'm just breaking it all into sections for simplicity's sake / to be more clear. This probably isn't a terribly hard problem, but I'm having trouble with it, so there will definately be a star for anyone that can figure this out for me!

DESCRIPTION OF SITUATION / WHAT I NEED TO DO
I have information spread across the 5 tables above. We have several companies that can purchase the use of web applications from us for their customers. Each client company has separate applications they can use (based on what they pay to use). So client One could have 5 applications for their users, client Two could have 7 applications to use, and so on. Now, each of the Client's customers are authorized to use some, but rarely all of the applications. So, Client One could have 10 customers with their 5 applications, and may have 2 customers to each application, or any mixture (i.e. all Ten customers use one application, while only One customer uses another, and Five use another.)

I need to be able to list all applications for the specific client logged in, and the users authorized to use each application for that client. (So if someone from client One is logged in, I need to be able to list all 5 applications for it, and then all 10 users, each under every application they are authorized for. So it may look something like this:

Application One
________________
- User 1
- User 2
- User 3
- User 4
- User 5

Application Two
________________
- User 1
- User 2
- User 6
- User 7
- User 8
- User 9
- User 10

Application Three
________________
- User 1
- User 3
- User 7
- User 10
etc.

DESCRIPTION OF DATA / SETUP

I'm dealing with 5 tables:
1. CP_CLIENT - client company's profile information
2. NAVIGATION - all applications
3. SECURITY_APPLICATION - applications associated with each specific client
4. WEB_SECURITY - lists which client is registered for which application
5. WEB_USER - lists the registered customers/users...all of them.

CP_CLIENT is related to NAVIGATION on CP_CLIENT.site_id = NAVIGATION.site_id

NAVIGATION is related to SECURITY_APPLICATION on NAVIGATION.id = SECURITY_APPLICATION.application_id

SECURITY_APPLICATION is related to WEB_SECURITY on SECURITY_APPLICATION.application_id = WEB_SECURITY.application_id

WEB_SECURITY is related to CP_CLIENT on WEB_SECURITY.client_id = CP_CLIENT.client_id

WEB_SECURITY is related to WEB_USER on WEB_SECURITY.user_id = WEB_USER.user_id

So as you can see, four of the tables have a circle reference from CP_CLIENT to NAVIGATION to SECURITY_APPLICATION to WEB_SECURITY and back to CP_CLIENT again, and the fifth is branched off of WEB_SECURITY.

*******
When logged in, the client id will be known (i.e. client_id = 2). That finds the correct profile in the CP_CLIENT table. Then it needs to pull all records that are on the navigation table that match the site_id of the client in CP_CLIENT table. It needs to further narrow that list down by selecting only applications out of that list that are listed in the SECURITY_APPLICATION table...whatever applications are listed in the SECURITY_APPLICATION table that match records on the NAVIGATION table where the NAVIGATION table site_id field matches the currently logged in client's site_id.

Now that the applications are found, I need to be able to pull which users are authorized for those applications; but, I need to still list the applications, even if no user's are authorized for them (i.e. returns application title, and null value for the user), and only pull those users that are also associated with the currently logged in client.

Then, lastly, I just need to pull the user_name from the WEB_USER table to get the actual user name, rather than just their ID.
*******

What I have so far
Here is the code I've come up with so far; however, it doesn't work exactly right, although it is close. What happens is it will list all users and applications appropriately, including null values for users when none are authorized for it, only, it'll list all users and applications for any client that is logged in that has the same site_id as another.

So for instance, if Client One and Client Two both have a site_id of 3, all users and applications that are associated with a client of site_id 3 will always be listed, and I can't seem to narrow it down to only list those clients that are associated with the currently logged in client.

Here is the code:

Code:

CREATE PROCEDURE sql_getSecurityApplicationAssignments
	@strClientID varchar(6)
AS
SET NOCOUNT ON
SELECT DISTINCT NAVIGATION.id, NAVIGATION.title, WEB_SECURITY.user_id, WEB_USER.user_name, CP_CLIENT.site_id
FROM         WEB_USER INNER JOIN
                      WEB_SECURITY ON WEB_USER.user_id = WEB_SECURITY.user_id RIGHT OUTER JOIN
                      CP_CLIENT INNER JOIN
                      NAVIGATION ON CP_CLIENT.site_id = NAVIGATION.site_id INNER JOIN
                      SECURITY_APPLICATION ON NAVIGATION.id = SECURITY_APPLICATION.application_id ON WEB_SECURITY.application_id = NAVIGATION.id
WHERE     (CP_CLIENT.client_id = @strClientID)
ORDER BY NAVIGATION.title, WEB_USER.user_name

Once again, I am sorry this is a bit long, but I wanted to make sure I described it very clearly.

-Ovatvvon :-Q

SatishPutcha · Sep 1, 2005

Hi Ovatvvon

I have created a query here. I hope this works. Please do check the syntax little though.

Code:

SELECT [FIELD NAMES]
FROM ((((CP_CLIENT 
INNER JOIN WEB_SECURITY ON WEB_SECURITY.client_id = CP_CLIENT.client_id)
INNER JOIN WEB_USER ON WEB_SECURITY.user_id = WEB_USER.user_id)
INNER JOIN SECURITY_APPLICATION ON WEB_SECURITY.application_id = SECURITY_APPLICATION.application_id)
INNER JOIN NAVIGATION ON SECURITY_APPLICATION.application_id = NAVIGATION.id)
WHERE CP_CLIENT.client_id = @strClientID

Ovatvvon · Sep 1, 2005

Hello 10091976,

unfortunately that doesn't work. It doesn't return the application titles where no user is assigned to them in the WEB_SECURITY table...it only returns the apps where they are assigned to them. This is the same problem I was running into half the time. Not sure how to get around this.

-Ovatvvon :-Q

DotNetGnat · Sep 1, 2005

Ovatvvon,

your explanation was good...but i got lost in it reading till the end...

can you just post some sample data from all your 5 tables and the kind of results you want...

-DNG

SatishPutcha · Sep 1, 2005

Ovatvvon,

Thats because of the INNER JOIN. It takes only those entries that are found in both tables. Instead use RIGHT OR LEFT and it will return all values for a certain ID whether found in other table or not.

Regards
Satish

Ovatvvon · Sep 1, 2005

DNG,

From the code I posted, for the client_id 'T02076' the results are as follows (and this is exactly what they should be, but I could take site_id off the retrieval list):

29 records said:
id | title | user_id | user_name | site_id
______________________________________________
127 | Applicant Status | Null | Null | 3
412 | Benefit Reports | bcarr | Bobby Carr | 3
412 | Benefit Reports | cyerger | Clyde Yerger | 3
413 | Deduction Reports | bcarr | Bobby Carr | 3
413 | Deduction Reports | cyerger | Clyde Yerger | 3
413 | Deduction Reports | cthorne | Corinthian Thorne | 3
317 | Driver Settlement | bcarr | Bobby Carr | 3
317 | Driver Settlement | cyerger | Clyde Yerger | 3
317 | Driver Settlement | cthorne | Corinthian Thorne | 3
130 | Employee Files | bcarr | Bobby Carr | 3
130 | Employee Files | cyerger | Clyde Yerger | 3
130 | Employee Files | cthorne | Corinthian Thorne | 3
130 | Employee Files | deboraharellano | Deborah Arellano |3
130 | Employee Files | joeadkins | Joe Adkins | 3
414 | HR Reports | bcarr | Bobby Carr | 3
414 | HR Reports | cyerger | Clyde Yerger | 3
414 | HR Reports | cthorne | Corinthian Thorne | 3
414 | HR Reports | deboraharellano | Deborah Arellano | 3
414 | HR Reports | joeadkins | Joe Adkins | 3
131 | Payroll Processing | bcarr | Bobby Carr | 3
131 | Payroll Processing | cyerger | Clyde Yerger | 3
131 | Payroll Processing | cthorne | Corinthian Thorne | 3
131 | Payroll Processing | deboraharellano | Deborah Arellano | 3
131 | Payroll Processing | joeadkins | Joe Adkins | 3
407 | Personnel Compliance | bcarr | Bobby Carr | 3
407 | Personnel Compliance | cyerger | Clyde Yerger | 3
407 | Personnel Compliance | cthorne | Corinthian Thorne | 3
407 | Personnel Compliance | deboraharellano | Deborah Arellano | 3
407 | Personnel Compliance | joeadkins | Joe Adkins | 3

Now, the problem is when I change to another client, for instance, T02191, which has the same site_id on the CP_CLIENT table as the client T02076...both have a site_id of 3. When I type in either one, I receive the exact same results. The above data should only appear for the client T02076, not T02191. (Quick FYI: the users above are listed in the WEB_SECURITY table. So far I've only entered users for the T02076 client, which is why I know they should not show up for anything else.)

When I run the query with the client id of T020191, all that should show up are the 8 applications that are available (listed in the SECURITY_APPLICATION table):

1. Applicant Status
2. Benefit Reports
3. Deduction Reports
4. Driver Settlement
5. Employee Files
6. HR Reports
7. Payroll Processing (and)
8. Personnel Compliance

with all null values for the user_id and user_name fields.

Hope this makes more sense. If not, please let me know and I'll try to make it more clear.

-Ovatvvon :-Q

DotNetGnat · Sep 1, 2005

ok i was looking at the query you posted....check it again...did you miss anything at the ? marks...may be a join...or may be i am missing something here

Code:

CREATE PROCEDURE sql_getSecurityApplicationAssignments
    @strClientID varchar(6)
AS
SET NOCOUNT ON
SELECT DISTINCT NAVIGATION.id, NAVIGATION.title, 
WEB_SECURITY.user_id, WEB_USER.user_name, CP_CLIENT.site_id
FROM         
WEB_USER INNER JOIN WEB_SECURITY ON 
WEB_USER.user_id = WEB_SECURITY.user_id 

RIGHT OUTER JOIN

CP_CLIENT INNER JOIN NAVIGATION ON 
CP_CLIENT.site_id = NAVIGATION.site_id 

INNER JOIN

SECURITY_APPLICATION ON 

NAVIGATION.id = SECURITY_APPLICATION.application_id 

[red]?????[/red] ON 

WEB_SECURITY.application_id = NAVIGATION.id

WHERE     (CP_CLIENT.client_id = @strClientID)
ORDER BY NAVIGATION.title, WEB_USER.user_name

-DNG

DotNetGnat · Sep 1, 2005

and i think...after looking at your results...first you should join on client id and then on the site id...to get all related to client id...

but you are doing the other way round...first you are doing the join on site id...and then join on client id...and thats the reason you are seeing same information for both the client id you mentioned...

just rearrange your query and you will be good to go..

-DNG

Ovatvvon · Sep 1, 2005

But I think that is where the problem is. If I do an INNER JOIN between the CP_CLIENT table and WEB_SECURITY table On client_id, I think it'll only pull records that match which users are in the WEB_SECURITY table, and I need it to pull all appripriate applications for the client, and then list all users for each application, even if one application has no users associated with it.

So, for instance, I have to join the CP_CLIENT table at two points, once on WEB_SECURITY.client_id, and then also on the NAVIGATION table where site_id's match.

To explain one more thing a little better than I have before, the NAVIGATION table holds all applications, period. Now, many of those applications are accessible to clients, but not all. So, the ones that are accessible are listed in the SECURITY_APPLICATION table. Since many applications are listed in the SECURITY_APPLICATION table that match records on the NAVIGATION table from multiple different site_id values, we want only those that match the site_id value of the current client, which is in the CP_CLIENT table. So there needs to be an INNER JOIN between CP_CLIENT, NAVIGATION and SECURITY_APPLICATION.

Now, we want to list all of those applications in the SECURITY_APPLICATION table, But, also list all users that are tied to each application from the WEB_SECURITY table. (the WEB_SECURITY table has a field called application_id that will match an application id from the SECURITY_APPLICATION table, and on that same record lists the user_id and client_id, so that the user is known from the WEB_USER table, and the affiliated client is known from the CP_CLIENT table.) So, I believe this is going to have to be an OUTER JOIN, so that it lists all users associated with each application for the currently logged in client (T02076).

The only thing that seems to be throwing it off now is that, not only do we want All users for each application (and we want all applications to be listed, regardless), we also only want users from the WEB_SECURITY table who's client_id match the client id of the currently logged in client (T02076).

If you take all this in combination with what I posted in my first post, it should make sense. WEB_SECURITY needs an INNER JOIN with CP_CLIENT, but an OUTER JOIN with SECURITY_APPLICATION, while CP_CLIENT needs the aforementioned INNER JOIN to the WEB_SECURITY table, but also needs an INNER JOIN to the NAVIGATION table...it goes in a circle, and I can't seem to get it to work. :-(

-Ovatvvon :-Q

Ovatvvon · Sep 1, 2005

Ok, I changed the code a bit, so now it looks like this:

Code:

SELECT DISTINCT NAVIGATION.id, NAVIGATION.title, WEB_SECURITY.user_id, WEB_USER.user_name
FROM NAVIGATION
	INNER JOIN CP_CLIENT ON NAVIGATION.site_id = CP_CLIENT.site_id
	INNER JOIN SECURITY_APPLICATION ON NAVIGATION.id = SECURITY_APPLICATION.application_id
	LEFT OUTER JOIN WEB_SECURITY ON SECURITY_APPLICATION.application_id = WEB_SECURITY.application_id
	LEFT OUTER JOIN WEB_USER ON WEB_SECURITY.user_id = WEB_USER.user_id
WHERE     WEB_SECURITY.client_id = 'T02076'
ORDER BY NAVIGATION.title, WEB_SECURITY.user_id

Now, it will always pull the correct users for the correct applications. The only thing it won't do is pull ALL applications, even if there is a null value for the users.

Can you see a way to change this so that all applications from the SECURITY_APPLICATION table are pulled?

-Ovatvvon :-Q

SatishPutcha · Sep 1, 2005

Make all the INNER JOIN into RIGHT or LEFT JOIN.

Regards
Satish

Ovatvvon · Sep 1, 2005

I get the same results

-Ovatvvon :-Q

SatishPutcha · Sep 1, 2005

Assuming you made all LEFT JOINS change this

Code:

INNER JOIN SECURITY_APPLICATION ON NAVIGATION.id = SECURITY_APPLICATION.application_id

and make it

Code:

LEFT JOIN SECURITY_APPLICATION ON SECURITY_APPLICATION.application_id = NAVIGATION.id

Regards
Satish

Ovatvvon · Sep 1, 2005

Still get the same results. 28 rows instead of the 20th one listing Applicant Status with Null user_id and user_name values.

-Ovatvvon :-Q

Ovatvvon · Sep 1, 2005

** Meant to type "29th", not "20th"

-Ovatvvon :-Q

DotNetGnat · Sep 1, 2005

you need LEFT OUTER JOIN

post the present query you are using...

-DNG

Ovatvvon · Sep 1, 2005

Ok, here is the code after making the alterations that 10091976 suggested:

Code:

SELECT DISTINCT NAVIGATION.id, NAVIGATION.title, WEB_SECURITY.user_id, WEB_USER.user_name
FROM NAVIGATION
	LEFT JOIN CP_CLIENT ON NAVIGATION.site_id = CP_CLIENT.site_id
	LEFT JOIN SECURITY_APPLICATION ON SECURITY_APPLICATION.application_id = NAVIGATION.id
	LEFT OUTER JOIN WEB_SECURITY ON SECURITY_APPLICATION.application_id = WEB_SECURITY.application_id
	LEFT OUTER JOIN WEB_USER ON WEB_SECURITY.user_id = WEB_USER.user_id
WHERE     WEB_SECURITY.client_id = 'T02076'
ORDER BY NAVIGATION.title, WEB_SECURITY.user_id

Here is after DNG's:

Code:

SELECT DISTINCT NAVIGATION.id, NAVIGATION.title, WEB_SECURITY.user_id, WEB_USER.user_name
FROM NAVIGATION
	LEFT OUTER JOIN CP_CLIENT ON NAVIGATION.site_id = CP_CLIENT.site_id
	LEFT OUTER JOIN SECURITY_APPLICATION ON SECURITY_APPLICATION.application_id = NAVIGATION.id
	LEFT OUTER JOIN WEB_SECURITY ON SECURITY_APPLICATION.application_id = WEB_SECURITY.application_id
	LEFT OUTER JOIN WEB_USER ON WEB_SECURITY.user_id = WEB_USER.user_id
WHERE     WEB_SECURITY.client_id = 'T02076'
ORDER BY NAVIGATION.title, WEB_SECURITY.user_id

Both cases only show the current clients in the WEB_SECURITY table. However, I did change the WHERE clause to look for the client_id in the WEB_SECURITY table rather than the CP_CLIENT table, which is what I had in my very first post. But, whenever I use the CP_CLIENT.client_id (instead of the WEB_SECURITY table's client_id field), any client with the same site_id has the same results posted. It is only when I look for the WEB_SECURITY.client_id that it pulls the right users, but doesn't list all applications.

When I tried the CP_CLIENT.client_id with the Left and Left Outer Joins, I retrieve a ton of applications from the NAVIGATION table that I do not want, and are not listed in the SECURITY_APPLICATION table. So, I changed them back to INNER JOIN's....at least for the time-being.

-Ovatvvon :-Q

DotNetGnat · Sep 1, 2005

try this:

Code:

SELECT DISTINCT NAVIGATION.id, NAVIGATION.title, WEB_SECURITY.user_id, WEB_USER.user_name
FROM CP_Client
    LEFT OUTER JOIN Navigation ON CP_CLIENT.site_id=NAVIGATION.site_id
    LEFT OUTER JOIN SECURITY_APPLICATION ON NAVIGATION.id
=SECURITY_APPLICATION.application_id 
INNER JOIN WEB_SECURITY ON SECURITY_APPLICATION.application_id = WEB_SECURITY.application_id
    INNER JOIN WEB_USER ON WEB_SECURITY.user_id = WEB_USER.user_id
WHERE WEB_SECURITY.client_id = 'T02076'
ORDER BY NAVIGATION.title, WEB_SECURITY.user_id

I am still confused with your table structures...can u please post some sample data from all the 5 tables and the kind of results you are looking for...

-DNG

Ovatvvon · Sep 1, 2005

Nope, :-( Same results as before. Lists all users, just not the Null valued Applications (in this case, there is only one: Applicant Status.)

Sorry for not splitting up among the tables before. Here are the tables with the relevant fields / information (there are more fields in each table, but I'm just including the ones that are used in this query to save space and for simplicity):

CP_CLIENT (contains Client information)
____________________________________________
client_id | site_id
-----------------------------
T02076 | 3
T02091 | 5
T02191 | 3

NAVIGATION (contains all web applications, whether accessible to the client or not)
____________________________________________
id | title | site_id
-----------------------------
120 | Home | 1
190 | Departments | 2
188 | Directories | 2
194 | Document Zone | 2
169 | Flex Plans | 4

121 | Administrative Services | 3
127 | Applicant Status | 3
412 | Benefit Reports | 3
413 | Deduction Reports | 3
317 | Driver Settlements | 3
130 | Employee Files | 3
414 | HR Reports | 3
131 | Payroll Processing | 3
407 | Personnel Compliance | 3

222 | Administrative Services | 5
488 | Applicant Status | 5
510 | Benefit Reports | 5
161 | Deduction Reports | 5
399 | Driver Settlements | 5
277 | Employee Files | 5
278 | HR Reports | 5
300 | Payroll Processing | 5
400 | Personnel Compliance | 5

SECURITY_APPLICATION (contains Application ID's of Apps in the NAVIGATION table that will be allowed to be accessible by users)
____________________________________________
id | application_id
-----------------------------
1 | 121
2 | 127
3 | 412
4 | 413
5 | 317
6 | 130
7 | 414
8 | 131
9 | 407

10 | 222
11 | 488
12 | 510
13 | 161
14 | 399
15 | 277
16 | 278
17 | 300
18 | 400

WEB_USER (contains all users that are registered to access these applications)
____________________________________________
user_id | employee_id |user_name
-----------------------------
bahlenius | I34905 | Brad Ahlenius
bcarr | G43686 | Bobby Carr
cthorne | A44136 | Corinthian Thorne
cyerger | G42366 | Clyde Yerger
deboraharellano | X46867 | Deborah Arellano
joeadkins | X33790 | Joe Adkins
ltillman | K43834 | Lasha Tillman
maliff | Y46076 | Mark Aliff
rogeralig | V44947 | Roger Alig
sbright | N34454 | Sean Bright

WEB_SECURITY (contains all records of which applications each user is authorized to use)
____________________________________________
user_id | client_id | application_id
---------------------------------------
bcarr | T02076 | 130
bcarr | T02076 | 131
bcarr | T02076 | 317
bcarr | T02076 | 407
bcarr | T02076 | 412
bcarr | T02076 | 413
bcarr | T02076 | 414
cthorne | T02076 | 130
cthorne | T02076 | 131
cthorne | T02076 | 317
cyerger | T02076 | 412
cyerger | T02076 | 413
cyerger | T02076 | 414

etc....

The desired output is displayed in a previous post of mine on this page: The 6th post on this page, My 3rd post on this page. Only, when a different client is logged online, only users in the WEB_SECURITY table that have a client_id that match that client that is logged online (same client_id as in the CP_CLIENT table) should be listed....not everyone in the WEB_SECURITY table. So, when the client T02076 is signed in, all the users listed above in the WEB_SECURITY table (along with others with the same client_id) should be listed, but when Client T02191 is logged in, only their users should be visible, not any of those with the client_id of T02076.

Does make more sense?

-Ovatvvon :-Q

DotNetGnat · Sep 1, 2005

By using your CP_CLIENT and NAVIGATION table there is no way to tell which id is referring to which client if the clients have the same site id...

i think the tables are not structured properly...

-DNG

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Tying together 5 tables to retrieve data - a bit long, but very clear 1

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor