Concatenate in select based on values in another table

[KazimRajani]

Hello everyone, 

I need to display list of resources available for specific tasks. The list needs to display all employees and should satisfy the following;

1. All employee should be displayed
2. Employee available for task should be selectable
3. Employee unavailable for task should also be shown but not selectable or not be able to assign a task
4. List of employees should be distinct
5. Available or Unavailable should be added to the employee in the form of Employee - Available or Employee - Unavailable.

The user will click the task from a given list, and upon clicking will be taken to a form or detail view where a dropdown list will be given of the employee. They will then select the employee from the given list.

I need help with 3 and 5.  I am stuck as to what's the best way to meet 2 & 3 based on my table structure. I can show the employee that is available for a task, but how do I show an employee record that isn't available for the selected task? In SQL, I would have done an outer join, but unsure how to do it in App Sheet. 

For point 5, I am unable to do a concatenation of two fields inside a select statement, perhaps because of inexperience with the platform. 

I have got the following tables, Employee_Task_Availability table below contains employee available for a task. 

Employee table (master) 

Emploeyee_ID (Unique)	Employee_Name
1	Fred Smith
2	Scott Neil

Task table (master)

Task_ID	Task_Name
1	Deliver Hardware
2	Configure Hardware

Employee_Task_Availabilty 

Task_ID	Task_Name	Employee_ID
1	Deliver Hardware	1	Fred Smith
2	Configure Hardware	2	Scott Neil
1	Deliver Hardware	2	Scott Neil

Thanks
Kaz

[Joseph_Seddik]

SQL Joins can be achieved in AppSheet by a combination of table lookup commands like SELECT() and List Expressions. I really prefer AppSheet’s way that allows for simpler, better readable expressions, yet both ways achieve the purpose. 

Please have a look at these guides:

List Addition | AppSheet Help Center
SELECT() | AppSheet Help Center
List Subtraction | AppSheet Help Center
INTERSECT() | AppSheet Help Center
List Expressions | AppSheet Help Center