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();
You need a LEFT join and the condition for the date in the ON clause:
SELECT *
FROM officehour o LEFT JOIN closed c
ON o.doc_id = c.doc_id AND date_format(c.closed_date,'%W') = o.weekday;
See the demo.