Hard SQL Query

[rigid tundra]

I have these two tables: one has some records and the 2nd has almost same records but with high priority.

I need to get all records from the 1st table, except when there are ones in the 2nd which override the ones in the 1st, in which case records from 2nd would replace the ones from 1st.

So I need to get all records in the 1st table and all records from 2nd but replace records from 1st with that from 2nd when they match.

How DB structure works:

1. E will contains rows and SE will be empty
2. on each "update", I'll add a row in SE that references its target in E
3. on each "delete", same thing but these rows are to be excluded from result, easy to do
4. on each "add", I only add a new row to SE and the foreign key will be null

It seems like it can't be don't 100% in SQL, but then SQL seems to have lots of logical keywords, maybe it's a matter of time.

I imagine it would be outer join to get all unmatched and then some logic to get only matched from 2nd table.

It doesn't matter if Relations or simple Eloquent syntax or raw SQL.

I have no clue why it did what it did but when I ran:

select E.id as id, SE.dictionary_entry_id as id, SE.action
from dictionaries_entries E left outer join dictionaries_soft_entries SE
on E.id=SE.dictionary_entry_id
order by E.id;

Although it should give me only the unmatched rows from the left, it gave me all the rows, and somehow, when I selected the foreign key columns from the right, they just aligned correctly with the primary keys from the left 🤯

Now, somehow again, when I try to exclude this NULLable column called action only when it's equal to "delete": where SE.action != 'delete', it also removed the ones that have NULL

I'm not that good with SQL, but this is so confusing to me!

[toxic lotus]

SELECT
COALESCE(SE.id, E.id) as id,
COALESCE(SE.some_field, E.some_field) as some_field,
COALESCE(SE.other_field, E.other_field) as other_field
FROM
dictionaries_entries E
LEFT OUTER JOIN
dictionaries_soft_entries SE
ON
E.id = SE.dictionary_entry_id
WHERE
(SE.action IS NULL OR SE.action != 'delete')
ORDER BY
E.id;

Explanation:

•    COALESCE(SE.id, E.id): This function returns the first non-null value in the list. If there’s a matching record in SE, it will use the SE record; otherwise, it uses the E record.
•    LEFT OUTER JOIN: This is correct because you want all records from E regardless of whether there’s a matching SE record.
•    WHERE (SE.action IS NULL OR SE.action != 'delete'): This excludes records where SE.action is 'delete'. If SE.action is NULL, the E record should be included.

That should work for you on what you want to do

[rigid tundra]

This coalesce worked really well. But I need some way to include the unmatching from the 2nd/right table as well:

mysql> SELECT
    ->     COALESCE(SE.id, E.id) as id,
    ->     COALESCE(SE.front, E.front) as front,
    ->     COALESCE(SE.back, E.back) as back
    -> FROM
    ->     dictionaries_entries E
    -> LEFT OUTER JOIN
    ->     dictionaries_soft_entries SE
    -> ON
    ->     E.id = SE.dictionary_entry_id
    -> WHERE
    ->     (SE.action IS NULL OR SE.action != 'delete')
    -> ORDER BY
    ->     E.id;
+--------------------------------------+-------+------+--------+
| id                                   | front | back | action |
+--------------------------------------+-------+------+--------+
| 9cb9544f-91b6-4cef-b8b0-f4307fe11e2e | Thema | Look | update |
| 9cb9544f-6696-4581-8963-4a5bc3517276 | Thema | Look | NULL   |
| 9cb9544f-6836-4e6e-8012-37b336345406 | Thema | Look | NULL   |
| 9cb9544f-69ad-4ca3-b6a4-2b8b6f29ae6c | Thema | Look | NULL   |
| 9cb9544f-6bbe-43ee-af1d-619cceee8b96 | Thema | Look | update |
| 9cb9544f-6d59-4819-b21c-8ac3e6ec2420 | Thema | Look | NULL   |
| 9cb9544f-6f4b-455d-853e-78ca0fdaa228 | Thema | Look | NULL   |
| 9cb9544f-705b-4aa1-a7c6-f41a495dd77a | Thema | Look | NULL   |
[REMOVED SOME ROWS DUE TO DISCORD LIMITATION ON NUMBER OF CHARACTERS]
+--------------------------------------+-------+------+
25 rows

mysql> select count(*) from dictionaries_entries;
+----------+
| count(*) |
+----------+
|       30 |
+----------+
mysql> select action, count(action) from dictionaries_soft_entries group by action;
+--------+---------------+
| action | count(action) |
+--------+---------------+
| update |             2 |
| add    |             8 |
| delete |             5 |
+--------+---------------+

[toxic lotus]

Do you have a copy of your DB?

You will want to do a FULL OUTER JOIN

SELECT 
    COALESCE(SE.id, E.id) as id,
    COALESCE(SE.some_field, E.some_field) as some_field,
    COALESCE(SE.other_field, E.other_field) as other_field
FROM 
    dictionaries_entries E
FULL OUTER JOIN 
    dictionaries_soft_entries SE 
ON 
    E.id = SE.dictionary_entry_id 
WHERE 
    (SE.action IS NULL OR SE.action != 'delete')
ORDER BY 
    COALESCE(SE.id, E.id);

[rigid tundra]

MySQL doesn't have full outer join 😢
I've tried a union, but obviously I'm doing something wrong!

SELECT 
    COALESCE(SE.id, E.id) as id,
    COALESCE(SE.front, E.front) as front,
    COALESCE(SE.back, E.back) as back,
    SE.action
FROM 
    dictionaries_entries E
LEFT OUTER JOIN 
    dictionaries_soft_entries SE 
ON  E.id = SE.dictionary_entry_id 
UNION
SELECT
    count(*)
FROM 
    dictionaries_entries E
RIGHT OUTER JOIN 
    dictionaries_soft_entries SE 
ON  E.id = SE.dictionary_entry_id 
WHERE 
    (SE.action IS NULL OR SE.action != 'delete')
ORDER BY 
    COALESCE(SE.id, E.id);

Err:
ERROR 1250 (42000): Table 'SE' from one of the SELECTs cannot be used in global ORDER clause

Sorry if this is inefficient: https://github.com/anasouardini/db_share (you only need dictionaries_entries and dictionaries_soft_entries)

I've used mydumper as mysqldump is really buggy on Debian.
This will import/run all sql files:for %S in (*.sql) do mysql -u user_name -p database_name < %S

[rigid tundra]

UPDATE: I figured I don't really need to have unmatching rows in the right table.

Thank you @toxic lotus , you solved my issue.

[toxic lotus]

Sure thing!