Search Join

The Search Join field gives you the ability to apply a specific LEFT JOIN between 2 or more tables .
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

This plugin allow "LEFT JOIN" query: return all rows from the left table, and the matched rows from the right table.
Options include Left Join (1), Left Join (2), Left Join (3). For each query 4 attributes must be filled:

• Left Table: Name of the first Table (ex: #__usergroups)
• Field Name of the common field in this first table (ex: id)
• Field Name of the common field in the second table (ex: group_id)
• Right Table: Name of the second Table (ex: #__user_usergroup_map)

The table on the right side of each Join line (parameter), is assumed to be already known/defined by the query.

Once installed, the Search Join field can be created and added to the Search Form view of a List & Search Type.

Example for a list of Users:
In order to filter all Users assigned to a specific User Group, let's setup a Search Join like this:
#__user_usergroup_map user_id id #__users
#__usergroups id group_id #__user_usergroup_map

Example for a list of Articles:
In order to filter all Articles created by a user assigned to a specific User Group, let's setup a Search Join like that:

#__users id created_by #__content
#__user_usergroup_map user_id id #__users
#__usergroups id group_id #__user_usergroup_map

This is not going to display anything on the Search Form.... but it will alter the SQL query.

In order to let the user select a specific User Group, you will need a 2nd field.. let's say a dynamic dropdown.
The storage of the field must be: Standard | User Group | id

Of course this is not specific to User Group, you can join the #__categories table or anything else..