venegoV
Deno•17mo ago
venego

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
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!
image.png
Was this page helpful?