Second Pre-Filter in DBJoin Element in Form?

[hominid4]

Hi, I have a dbjoin element(client_id) that creates a dropdown of the #__users table, with an element pre-filter so that in the form it only pulls the Clients who are assigned to #__usergroups called ?Clients? (GID = 11):

Data - where tab:
WHERE {thistable}.id IN (SELECT user_id FROM #__user_usergroup_map WHERE group_id = 11)
Apply where to: Public

That works correctly. I also have another #__usergroups called ?Employee? (GID = 13) and a ?portal_user_relationship? table that associates the employees with the clients:

==================
portal_user_relationship
==================
id | employee_id | client_id
1  | 365        | 501
2  | 874        | 506
3  | 365        | 538
4  | 471        | 590
5  | 471        | 611

When a Employee logs in, and views the form, I need for them to only see the clients they are associated with:

Data - where tab:
WHERE {thistable}.id IN (SELECT client_id FROM portal_user_relationship WHERE employee_id = {$my->id})
Apply where to: Employee

Each of those element pre-filters work on their own but I?m needing to combine them , so that if a ?Special? user logs in they see all "Clients" in the form?s dropdown (only the Clients not the full #__users table), but if an Employee logs in and goes to the form they only see their related clients.

I?ve been trying different subqueries and such but keep only getting an either/or result. Is it possible to start with a form element pre-filter showing the Clients, and then an "IF" pre-filter saying if an ?Employee? then add the second pre-filter based on the ?portal_user_relationship? table?

Thanks!

UPDATE: I've been trying if/then statements (which I may be way off base and not using it correctly at all) such as below but receiving errors such as "right syntax to use near ') THEN (".

WHERE {thistable}.id IN (SELECT user_id FROM #__user_usergroup_map WHERE group_id = 11) AND IF ({$my->id} IN (SELECT user_id FROM #__user_usergroup_map WHERE group_id = 13)) THEN {thistable}.id IN (SELECT client_id FROM portal_user_relationship WHERE employee_id = {$my->id});

 

[hominid4]

Hi, not sure if this is fully the correct way, but I was able to achieve the results I needed with:

WHERE {thistable}.id IN (
  CASE WHEN EXISTS
    (SELECT 1
    FROM #__user_usergroup_map
    WHERE user_id = {$my->id}
    AND group_id = 13)
  THEN
    (SELECT client_id
    FROM portal_user_relationship
    AS urel
    WHERE employee_id = {$my->id}
    AND urel.client_id = {thistable}.id)
  ELSE
    (SELECT user_id
    FROM #__user_usergroup_map
    AS ugroup
    WHERE group_id = 11
    AND ugroup.user_id = {thistable}.id) 
  END)

 

[cheesegrits]

If it works, it's the correct way.

-- hugh