Deno•2y ago

Hard SQL Query

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

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

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;

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

action

only when it's equal to "delete":

where SE.action != 'delete'

where SE.action != 'delete'

, it also removed the ones that have

NULL

NULL

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

Deno•2y ago

tarkov

Hard SQL Query

How DB structure works:

null

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

outer join

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;

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;

Now, somehow again, when I try to exclude this NULLable column called

action

action

only when it's equal to "delete":

where SE.action != 'delete'

where SE.action != 'delete'

, it also removed the ones that have

NULL

NULL

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

Hard SQL Query

How DB structure works:

Similar Threads

Hard SQL Query

How DB structure works:

Similar Threads

Similar Threads

Similar Threads