I have a query which lists users but it requires a sub query to get whether they are available today. I need the fourth column 'Availability' to iterate through each user and display if they have availability or display a null. I've tried everything I can think of sub queries, cursors etc but no joy. Any pointers welcome!

```
SELECT inter.authno,
       inter.FirstName,
       inter.Surname,
       COALESCE(( 
                 Select at.typeName as [Availability]
                 FROM [database].[dbo].[Interviewer] inter 
                 full join [database].[dbo].[availability] av 
                 on inter.authno = av.authno
                 full join [database].[dbo].[availability_days] ad 
                 on av.availID = ad.availID
                 full join [database].[dbo].[availibiltyType] at
                 on av.typeID = at.typeid
                 where exists(
                      select authno 
                      from [database].[dbo].[Interviewer]
                 )
                 and ad.actualDay = '2015-05-21'
                 ), null ) AS [Availability]

 FROM [database].[dbo].[Interviewer] inter

```

The query gives the below results, but it should only show Available for Harry Kane and the rest should be null.

```
authno FirstName Surname Availability
-------------------------------------
10     Minch     Yoda    Available 
11     Darth     Vadar   Available 
12     Darth     Maul    Available 
14     Obi Wan   Kenobi  Available
15     Qui-Gon   Jinn    Available
16     Darth     Sidious Available
17     Boba      Fett    Available
24     Harry     Kane    Available
39     mark      o'neill Available

```

I also tried the code suggestion below kindly provided which gives some results I need, but it shows all of the results instead of the availability type for today.

```
        SELECT 
    inter.authno,
    inter.FirstName,
    inter.Surname,
    at.typeName as [Availability]

    FROM [database].[dbo].[Interviewer] inter 

    left JOIN [database].[dbo].[availability] av
    on inter.authno = av.authno

    left JOIN [database].[dbo].[availability_days] ad 
    on av.availID = ad.availID 
    and ad.actualDay = '2015-07-21' 

    left JOIN [database].[dbo].[availibiltyType] at 
    on av.typeID = at.typeid

```

Output:

```
  +----+---------+-----------+--------------+
        | 10 |  Minch  |   Yoda    |     NULL     |
        +----+---------+-----------+--------------+
        | 11 | Darth   | Vadar     | NULL         |
        | 12 | Darth   | Maul      | NULL         |
        | 13 | Luke    | Skywalker | NULL         |
        | 14 | Obi Wan | Kenobi    | NULL         |
        | 15 | Qui-Gon | Jinn      | Annual Leave |
        | 16 | Darth   | Sidious   | NULL         |
        | 17 | Boba    | Fett      | UO           |
        | 17 | Boba    | Fett      | Available    |
        | 18 | test22  | test33    | NULL         |
        | 19 | test7   | test7     | NULL         |
        | 22 | Bob     | Marley    | NULL         |
        | 23 | JO      | JO        | NULL         |
        | 24 | Harry   | Kane      | Annual Leave |
        | 24 | Harry   | Kane      | Available    |
        | 24 | Harry   | Kane      | Available    |
        | 24 | Harry   | Kane      | Annual Leave |
        | 24 | Harry   | Kane      | Annual Leave |
        | 24 | Harry   | Kane      | NW           |
        | 24 | Harry   | Kane      | NW           |
        | 24 | Harry   | Kane      | Available    |
        | 39 | mark    | o'neill   | US           |
        +----+---------+-----------+--------------+

```

I also tried the below which gets me the exact results that I need only that, I need to display all users whether they have a date in the table or not. i.e. If I change the date to last weer Harry Kane disappears.

```
        SELECT 
    inter.authno,
    inter.FirstName,
    inter.Surname,
    at.typeName as [Availability]

    FROM [database].[dbo].[Interviewer] inter 

    left JOIN [database].[dbo].[availability] av
    on inter.authno = av.authno

    left JOIN [database].[dbo].[availability_days] ad 
    on av.availID = ad.availID 

    left JOIN [database].[dbo].[availibiltyType] at 
    on av.typeID = at.typeid

            where ad.actualDay = '2015-05-21'or ad.actualDay is null

```

Output for today:

```
    +--------+-----------+-----------+--------------+
    | authno | FirstName |  Surname  | Availability |
    +--------+-----------+-----------+--------------+
    |     10 | Minch     | Yoda      | NULL         |
    |     11 | Darth     | Vadar     | NULL         |
    |     12 | Darth     | Maul      | NULL         |
    |     13 | Luke      | Skywalker | NULL         |
    |     14 | Obi Wan   | Kenobi    | NULL         |
    |     16 | Darth     | Sidious   | NULL         |
    |     18 | test22    | test33    | NULL         |
    |     19 | test7     | test7     | NULL         |
    |     22 | Bob       | Marley    | NULL         |
    |     23 | JO        | JO        | NULL         |
    |     24 | Harry     | Kane      | Available    |
    +--------+-----------+-----------+--------------+

Output for 2015-05-10

+--------+-----------+-----------+--------------+
| authno | FirstName |  Surname  | Availability |
+--------+-----------+-----------+--------------+
|     10 | Minch     | Yoda      | NULL         |
|     11 | Darth     | Vadar     | NULL         |
|     12 | Darth     | Maul      | NULL         |
|     13 | Luke      | Skywalker | NULL         |
|     14 | Obi Wan   | Kenobi    | NULL         |
|     16 | Darth     | Sidious   | NULL         |
|     18 | test22    | test33    | NULL         |
|     19 | test7     | test7     | NULL         |
|     22 | Bob       | Marley    | NULL         |
|     23 | JO        | JO        | NULL         |
+--------+-----------+-----------+--------------+

```