How to use Weekday() function inside join in MySQL?

Question

I have 2 tables from which one is a parent & other one is child.For 1 order we have multiple records in child table.

For e.g: Order & order_frequency are 2 tables.

Order table data:

+-----+---------+---------------------+
| id  | product | orderDate           |
+-----+---------+---------------------+
| 100 | abc     | 2020-10-01 05:30:00 |
+-----+---------+---------------------+
| 200 | xyz     | 2020-10-01 05:30:00 |
+-----+---------+---------------------+

order_frequency table data:

+----+-------+-----------+
| id | ordID | frequency |
+----+-------+-----------+
| 1  | 100   | 111       |
+----+-------+-----------+
| 2  | 100   | 234       |
+----+-------+-----------+
| 3  | 200   | 111       |
+----+-------+-----------+
| 4  | 200   | 234       |
+----+-------+-----------+

I want to fetch matching frequency from order_frequency table based on order date(Which are falling on monday[111] & Tue,wed,thru[234]).

I have tried below query:

SELECT * FROM order LEFT JOIN order_frequency ON (order.id=order_frequency.ordID AND order_frequency.frequency like "%1%");

when i tried above query by providing hard coded value for frequency in column it provide me expected result.

But i want to fetch it dynamically by providing orderDate. something like below:

SELECT (weekday(DATE(orderDate))+1) AS weekday,* from order LEFT JOIN order_frequency ON (order_frequency.Day like CONCAT('%',@weekday,'%'))

Thanks in advance.

Answer 1

SELECT ...
FROM order o
JOIN order_frequency of ON CASE o.frequency 
                           WHEN 111 THEN WEEKDAY(of.orderDate)=0 
                           WHEN 234 THEN WEEKDAY(of.orderDate) BETWEN 1 AND 3
                           END
...