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

[Ovatvvon]

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:

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

 

[Ovatvvon]

by id, I think you mean application_id, correct?

If two clients have the same site_id (i.e. 3), then it is fine for them to have the same applications listed:
i.e.
1. Applicant Status, 2. Benefit Reports, 3. Deduction Reports, etc...

The part that I want to separate by client is which users are listed, and each user (in the WEB_SECURITY table) has their client_id [i.e. which client they are a part of] in their record. So, I want to list all applications associated with site_id 3 (or whatever is the appropriate site_id for the currently logged in client), and then list only the users listed in the WEB_SECURITY table that have a client_id that match the currently logged in client. It should work...just don't know how to do it.


-Ovatvvon :-Q

 

[DotNetGnat]

ok lets try this:

tell me what you get...

SELECT t1.id, t1.title, t3.user_id, t4.user_name
from NAVIGATION t1 INNER JOIN
CP_CLIENT t2 ON
t1.site_id=t2.site_id
INNER JOIN WEB_SECURITY t3
ON t2.client_id=t3.client_id
INNER WEB_USER t4
ON t3.user_id=t4.user_id
WHERE t2.client_id=T02076

-DNG

 

[Ovatvvon]

I get a listing of all records in the NAVIGATION table where the site_id matches the site_id on the CP_CLIENT table where the client_id on CP_CLIENT is 'T02076' (i.e. Where site_id = 3)

This returns approximately 1,540 rows of data

-Ovatvvon :-Q

 

[DotNetGnat]

oops...

from NAVIGATION t1 [redINNER[/red] JOIN
CP_CLIENT t2

use LEFT or LEFT OUTER JOIN instead and then tell me what you get...

-DNG

 

[Ovatvvon]

Tried both LEFT and LEFT OUTER; receive the same results. Wouldn't an OUTER join create more records by default though? I need 8 applications returned, and with all users listed for each application, currently there should be a total of 29 records.

-Ovatvvon :-Q

 

[DotNetGnat]

dude...i think we are making the blunder here...see this...

lets say we have two tables like this:

client

cid | site_id
_________________
T02076 | 3
T02091 | 5
T02191 | 3

and

Navigation

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

you dont want joins here....

you need to do something like this...

SELECT id, title, site_id FROM Navigation
WHERE site_id IN ( SELECT site_id from client WHERE cid='T02076')

then you will get 8 records...

-DNG

 

[DotNetGnat]

i tried all kinds of joins and i got all records from navigation table...

-DNG

 

[Ovatvvon]

hmmm, ok, I think you are on the trail....definately a nested query is in order. Now to get it working for everything....lemme try a few things (or if you come up with it first, by all means, post it.)


-Ovatvvon :-Q

 

[DotNetGnat]

from so long time we have been trying to join on something which is not a key??

are you getting what i said??

-DNG

 

[Ovatvvon]

You will always get all records from NAVIGATION unless you also join the SECURITY_APPLICATION table to the NAVIGATION table on an INNER JOIN on the fields SECURITY_APPLICATION.application_id = NAVIGATION.id.

But, I still think the nested query is necessary for this operation that I'm trying to do.

-Ovatvvon :-Q

 

[Ovatvvon]

PRIMARY KEYS:
__________________________________________________

CP_CLIENT.client
NAVIGATION.id
SECURITY_APPLICATION.application_id
WEB_SECURITY.user_id
WEB_SECURITY.client_id
WEB_SECURITY.application_id
WEB_USER.user_id

-Ovatvvon :-Q

 

[DotNetGnat]

that is better...we were doing this...

CP_CLIENT.site_id=NAVIGATION.site_id

which wont get you correct results...

-DNG

 

[Ovatvvon]

Maybe I'm not understanding something...

If you Join CP_CLIENT.site_id = NAVIGATION.site_id, then if CP_CLIENT.site_id = 3, won't that pull all records from NAVIGATION that have a site_id of 3?

And then, if we have 8 records listed in the SECURITY_APPLICATION table that correspond to 8 of the many records on the NAVIGATION table that have a site_id 0f 3, won't it then just pull out those 8 records?

-Ovatvvon :-Q

 

[DotNetGnat]

If you Join CP_CLIENT.site_id = NAVIGATION.site_id, then if CP_CLIENT.site_id = 3, won't that pull all records from NAVIGATION that have a site_id of 3?

if its not a primary key then results wont be correct....

thats what i was trying to say...

anyways try this and tell me what you get...

SELECT t1.client_id, t1.site_id,
t2.user_id, t3.id,t3.title
from
CP_CLIENT t1 INNER JOIN
WEB_SECURITY t2
ON t1.client_id=t2.client_id
INNER JOIN 
NAVIGATION t3 ON
t2.application_id=t3.id
WHERE t1.client_id='T02076'

-DNG

 

[Ovatvvon]

looks like the same thing; 28 records, still missing the null applicant status

-Ovatvvon :-Q

 

[DotNetGnat]

were you able to solve this...

-DNG

 

[Ovatvvon]

not yet

-Ovatvvon :-Q

 

[Ovatvvon]

Ok, I think I got it. Here is what I have:

CREATE PROCEDURE sql_getSecurityApplicationAssignments
	@strClientID varchar(6)
AS
SET NOCOUNT ON
SELECT tblApplications.id, tblApplications.title, tblUsers.user_id, tblUsers.user_name
FROM (SELECT DISTINCT NAVIGATION.id, NAVIGATION.title
			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
			WHERE CP_CLIENT.client_id = @strClientID) as tblApplications
	LEFT OUTER JOIN
			(SELECT DISTINCT WEB_SECURITY.user_id, WEB_USER.user_name, NAVIGATION.id, NAVIGATION.title
			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 WEB_SECURITY.client_id = @strClientID) as tblUsers
	ON tblApplications.id = tblUsers.id
ORDER BY tblApplications.title, tblUsers.user_name

Does anything with that look incorrect, wrong, etc.????

-Ovatvvon :-Q

 

[Ovatvvon]

Yep, after a bunch of testing, it looks like this is definately correct.

You got me pointed in the right direction, so even though you weren't able to solve it for me, it got me to the solution, so I'm going to give you a star for your help! (And, because of all the time you invested in it!)

-Ovatvvon :-Q