I have 2 tables:

**officehour**

| weekday | open | close | doc\_id |
| --- | --- | --- | --- |
| Monday | 16:00 | 19:00 | 1 |
| Tuesday | 08:00 | 11:00 | 1 |
| Tuesday | 15:00 | 19:00 | 2 |
| Thursday | 16:00 | 19:00 | 1 |
| Friday | 08:00 | 10:00 | 1 |
| Friday | 15:00 | 19:00 | 2 |
| Saturday | 08:00 | 11:00 | 2 |

**closed**

| closed\_date | open | close | closed | doc\_id | reason |
| --- | --- | --- | --- | --- | --- |
| 2022-08-23 | 16:00 | 17:00 | 0 | 2 | Interview |
| 2022-08-30 | null | null | 1 | 1 | Vacation |
| 2022-08-30 | 16:00 | 17:00 | 0 | 2 | Sleep In. |

I need all the records from officehour, so I thought I'd be using a left outer join and all the records from closed where the doc\_id matches AND the weekday matches for the closed date if closed.

There will never be an instance of having a closed record where there isn't an officehour record.

How can I get the following using mysql (or eloquent)?

| weekday | open | close | doc\_id | closed\_date | open | close | closed | reason |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Monday | 16:00 | 19:00 | 1 | null | null. | null. | null. | null. |
| Tuesday | 08:00 | 11:00 | 1 | 2022-08-30 | null | null | 1 | Vacation |
| Tuesday | 15:00 | 19:00 | 2 | 2022-08-23 | 16:00 | 17:00 | 0 | Interview |
| Tuesday | 15:00 | 19:00 | 2 | 2022-08-30 | 16:00 | 17:00 | 0 | Sleep In. |
| Thursday | 16:00 | 19:00 | 1 | null | null. | null. | null. | null. |
| Friday | 08:00 | 10:00 | 1 | null | null. | null. | null. | null. |
| Friday | 15:00 | 19:00 | 2 | null | null. | null. | null. | null. |
| Saturday | 08:00 | 11:00 | 2 | null | null. | null. | null. | null. |

I've tried a few things, but this is my most recent query:

```
$hours = DB::select("SELECT * from officehour a FULL OUTER JOIN closed b ON a.doc_id = b.doc_id WHERE date_format(b.closed_date,'W%') = a.weekday");
dd($hours);

```

I get the following error when I try to add a where clause:
`SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN closed b ON a.doc_id = b.doc_id WHERE date_format(closed_d' at line 1 (SQL: SELECT * from officehour a FULL OUTER JOIN closed b ON a.doc_id = b.doc_id WHERE date_format(b.closed_date,'W%') = weekday)`

I have also tried:
`DB::select("SELECT * from officehour a FULL OUTER JOIN closed b ON a.doc_id = b.doc_id WHERE date_format(b.closed_date,'W%') = a.weekday");`

I also read something that said I'm having syntax errors cause it doesn't like full outer joins, so I tried this:

```
$second = DB::table('closed')
            ->rightJoin('officehour', 'officehour.doctor_id', '=', 'closed.doctor_id');

$first = DB::table('officehour')
    ->leftJoin('closed', 'officehour.doctor_id', '=', 'closed.doctor_id')
    ->unionAll($second)
    ->get();

```