I have 2 tables:

officehour

closed

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)?

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();

CodePudding user response：

1. You never told SQL to open the table officehour as 'a'
2. You never told SQL to open the table closed as 'b'

Here is the statement.

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

CodePudding user response：

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.