Synchronizing N Values Accross Tables in SQL.

Question

Disclaimer: I just started with databases, so I'm probably missing something extremely trivial.

I have two (somewhat related, but not enough to be a single table) tables: table_one and table_two.

---

table_one has two columns of significance:

1. name of type varchar(n)
2. intersects of type bit

table_two has one column of significance:

1. name of type varchar(n)

---

I would like to automatically set intersects to either 0 or 1 depending on whether name is present in table_two.

Can I somehow delegate this responsibility to the database engine? I'm currently using MySQL, if that matters.

---

EDIT: Does this sort of close-coupling even make sense when dealing with databases?

Answer 1

ANSI SQL:

update table_one
set intersects = 
    case when 
        (select count(*) from table_two where name = table_one.name) > 0 
        then 1 
    else 0 end

MySQL Join:

update table_one t1
    left join table_two t2 on
        t1.name = t2.name
set t1.intersects = case when t2.name is not null then 1 else 0 end

You can put this query into a trigger to keep intersects up-to-date:

create trigger intersections after insert for each row
begin

    update table_one t1
        left join table_two t2 on
            t1.name = t2.name
    set t1.intersects = case when t2.name is not null then 1 else 0 end
    where t1.name = new.name

end

Now, for the design discussion:

An intersects column is great and all, but you may run into the issue where it gets out of sync. What you can do is use an exists function in your select when calling this:

select
    *
from
    table_one t1
where
    exists (select 1 from table_two t2 where t2.name = t1.name)

You can use not exists to find those that don't intersect, as well.

Moreover, you can also use a left join to pull back both cases:

select
    *
from
    table_one t1
    left join table_two t2 on
        t1.name = t2.name

Anything where t2.name is null, you don't have an intersection. Anything where it isn't, you do. Of course, this can produce duplicate rows if you don't have a unique constraint on name.

Hopefully this sheds some light on all of this!