assume i have 2 tables
this is my fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=d8526c5e4eb6e58eb8071b395223d690
shipping table :
+----+--------+----------+------------+------------+--------+
| id | origin | order_id | createdAt | product_id | amount |
+----+--------+----------+------------+------------+--------+
| 1 | 1 | 11 | 2020-12-02 | 234 | 2000 |
| 2 | 1 | 11 | 2020-12-02 | 235 | 3000 |
| 3 | 1 | 11 | 2020-12-02 | 236 | 4000 |
| 4 | 2 | 12 | 2020-12-02 | 236 | 3000 |
| 5 | 2 | 12 | 2020-12-02 | 235 | 2100 |
| 6 | 3 | 13 | 2020-12-02 | 236 | 2200 |
| 7 | 3 | 13 | 2020-12-02 | 239 | 3400 |
| 8 | 4 | 14 | 2020-12-02 | 237 | 2300 |
| 9 | 4 | 14 | 2020-12-02 | 233 | 4000 |
+----+--------+----------+------------+------------+--------+
reprint table :
+----+-----------+---------------------+--------+
| id | origin_id | reprintAt | status |
+----+-----------+---------------------+--------+
| 1 | 1 | 2020-12-03 06:53:53 | 1 |
| 2 | 1 | 2020-12-04 06:53:53 | 1 |
+----+-----------+---------------------+--------+
with shipping.origin = reprint.origin_id
basically table reprint is the condition when the origin_id want to print, then the record will be recorded in reprint table
so i want to make a table with the condition if the origin_id are in table reprint, then make it status reprint, else is NULL
so i tried with this query
SELECT shipping.origin, (SELECT IF(
origin_id = shipping.origin, 'REPRINT', '')
FROM reprint
JOIN shipping ON reprint.origin_id = shipping.origin
WHERE
origin_id = shipping.origin) status_reprint
FROM shipping
JOIN reprint ON shipping.origin = reprint.origin_id;
but it seems not in my expected result
my expected result is just like this based on the table
+-----------------+----------------+
| Shipping Origin | Status_Reprint |
+-----------------+----------------+
| 1 | |
| 1 | |
| 1 | |
| 1 | REPRINT |
| 1 | REPRINT |
| 1 | REPRINT |
| 1 | REPRINT |
| 1 | REPRINT |
| 1 | REPRINT |
| 2 | |
| 2 | |
| 3 | |
| 3 | |
| 4 | |
| 4 | |
+-----------------+----------------+
explanation : since origin_id = 1 reprint in two times on reprint table, and each origin_id contain 3 product_id, then it must be 3 origin_id with no reprint_status, and 6 origin_id with reprint_status = reprint because for 1 origin_id contain 3 product_id
