I am struggling with the following SQL query right now:
select uc.* from usercredential uc inner join users u on u.userid = uc.userid inner join credential c on c.credentialid = uc.credentialid inner join healthsystemcredential hsc on hsc.credentialid = c.credentialid inner join accesslevel ac on hsc.hscredentialid in ( -- Trying to figure out how to filter here? select 1 from dual ) where c.fulfillmentmethod = 'Attach' and c.display = 1 and uc.auditstatus <> 0 and ( (uc.auditstatus is null) or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) )
Basically I am trying to get UserCredential that are Many To One with Credential. Credential is One To Many with HealthSystemCredential.
The HealthSystemCredential has a foreign key to AccessLevel, however this AccessLevel column in HealthSystemCredential is actually the Lowest Allowable Access Level. AccessLevel records have a Type Rank between 1 at the lowest and 4 at the highest.
Example: A HealthSystemCredential with an AccessLevel record that has a Type Rank of 2 should be fetched when I filter my query by AccessLevels with Types of 2, 3, or 4.
The problem is I can't figure out how to do this in one query. Is there some way I can get a DENSE_RANK column in a subquery of AccessLevels, apply my filter in the subquery and join it to the table somehow to give me all the applicable HealthSystemCredentials I want?