Multiple reference SQL?...Need some help...

[davism]

Hi,

Any help would be greatly appreciated on this. I'm running out of ideas...

I will try my best to explain the situation.

I have a user that logs in then is USER E.

I have a table that has a reference to LOC A and LOC B but like A and B ID's. These LOC's are stored in another table.

The table has several rows:
ROW A - Idenfies LOC A and LOC B
ROW B - Identifies LOC B and LOC F
ROW C - Identifies LOC G and LOC H

LOC A and LOC B have value users associated to each LOC. LOC A has USER D and LOC B has USER C.

I have another table REL that associates USER E (a Child) to USER C (a Parent). USER I (a child) to USER D (a parent) NOTE: This is not family related just association related which is where the parent/child terms are from.)

I can drill down on the LOC B table and see it has USER C.

Now what I want to get at is the USER E logs in but I want the user to see ROW A (because USER E is associated in a way to LOC B) and ROW B (because the USER E is associated in a way to LOB B) and no other rows.

Next I want a user - let's say USER I to log in. USER I would only be able to see ROW A because they are not affiliated or related to ROW B or ROW C.

I know this can be a little confusing to explain and hope I did it some justice.

Any ideas on this?

Any info would be greatly appreciated.

Thanks

 

[r937]

totally lost trying to understand what you're doing

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Jamfool]

I am guessing that you are trying to model some sort of hierarchical structure?

So I will post some links to see if you can better explain what you are trying to achieve.

Recursive Queries Using Common Table Expressions

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Model Your Data Hierarchies With SQL Server 2008

http://msdn.microsoft.com/en-us/magazine/cc794278.aspx

 

[davism]

Actually, had some inappropriate information. I got it to work out though, I believe.

I know it was confusing and confused the heck out of me but when correct information was provided that seemed to resolve so many issues.

I believe I have it by doing a simple left join on a table that was already joined but I used an alias. I used an OR condition on a later INNER JOIN as well.

I am believing I have the answer but I will definitely let you know and if not, I will definitely clarify more.

Thanks!