Well, your first mistake is trying to write SQL code like it's VB code. SQL process sets, not records. It's a whole different paradigm. Don't try to apply conditions to each record as you step through the recordset. Apply conditions to groups of records.
First, I need to understand your conditions. If I'm reading this correctly, then any user with access level 'A' simply gets access to all companies, but they won't necessarily have all the companies listed in the UserSec table (which is creating an issue with the join). If the user has access level 'U' then they will have the appropriate companies listed in the UserSec table, and need only those companies returned.
Given the complication with the join in the case of A, I think you might be better off with a UNION query, treating them as two separate cases.
Let's see if I can take a crack at this.
SELECT T1.UserName, T1.UserName2, T1.AccessLvl, T3.CompanyID, T3.CompanyName
FROM dbo.Users T1
JOIN dbo.Company T3
WHERE T1.AccessLevel = 'A'
UNION
SELECT T1.UserName, T1.UserName2, T1.AccessLvl, T3.CompanyID, T3.CompanyName
FROM dbo.Users T1
JOIN dbo.UserSec T2
ON T1.UserName2 = T2.UserName
JOIN dbo.Company T3
ON T2.Company = T3.CompanyName
WHERE T1.AccessLvl = 'U'
Let me know if that works.
IP Logged
