well you have 4 different ways to merge a table.
- First of all, you can create a query, which gives you the result as if it was a merge:
a simple join:
tsql
- or you may want to create a view, which is a new table which values are update in real time by a query:
using a view:
create or replace view t3 as
select ssn, t1.name, t2.last, t2.ad, MAX(t1.sal, t2.sal)
from t1, t2
where t1.ssn = t2.ssn;
- or third option, you may want to create a new table based on the values of the previous one:
or create a new table, and insert the values of the two others
create table t3 (ssn, name, last, ad, sal);
insert into t3 select ssn, t1.name, t2.last, t2.ad, MAX(t1.sal, t2.sal)
from t1, t2
where t1.ssn = t2.ssn;
you got the choice of the weapons! 🙂
about the query, you may want to make a more complex join than the one I wrote in my examples, i.e.:
select *
from t1, t2
where t1.ssn = t2.ssn
and t1.name = t2.name
and (t1.last = t2.last
or t1.ad = t2.ad
or t1.sal = t2.sal);
as I don’t really get the logic in your t3 join table’s values, I don’t really see what you’d want as a condition. like the column ad that has the abc string which comes from nowhere. But you may replace AND by OR in the previous query to change the selection behaviour, and add IF() expressions to select a value…
ok, then here’s the reviewed query that may suit your needs (if sal is a number):
select t1.ssn, t1.name, t2.last, t2.ad, MAX(t1.sal, t2.sal)
from t1, t2
where t1.ssn = t2.ssn;
or
select t1.ssn as ssn,
t1.name as name,
t2.last as last ,
t2.ad as ad,
if(t1.sal is null,t2.sal,t1.sal) as sal
from t1, t2
where t1.ssn = t2.ssn;
or
select t1.ssn as ssn,
t1.name as name,
t2.last as last ,
t2.ad as ad,
case when s1.sal is null
then s2.sal
else s1.sal
end as sal
from t1, t2
where t1.ssn = t2.ssn;
- In oracle and postgresql (but not mysql and sqlite), you also have the merge statement.