Join multiple files with SQL

you can but it requires doing a check (i.e. double hit on one of the tables)

SELECT a.field1, b.shpdt
  FROM myfile a JOIN current b ON a.field1 = b.field1
UNION ALL
SELECT a.field1, b.shpdt
  FROM     myfile a
       JOIN
           history b
       ON a.field1 = b.field1 AND a.field1 NOT IN (SELECT field1 FROM current)

another option,  if the field1 might exist in both,  choose the later one
you could change MAX to MIN or use some other logic as necessary


SELECT a.field1, b.shpdt
  FROM     myfile a
       JOIN
           (SELECT field1, MAX(shpdt) shpdt
              FROM (SELECT field1, shpdt FROM current
                    UNION ALL
                    SELECT field1, shpdt FROM history) as b) as b
       ON a.field1 = b.field1

it just occurred to me,  if option #2 works,  then option #1 will work too with out the IN check
if field1 only exists in one of the current or history tables and never both, then this will work
and is probably the most efficient (if appropriate)

SELECT a.field1, b.shpdt
  FROM myfile a JOIN current b ON a.field1 = b.field1
UNION ALL
SELECT a.field1, b.shpdt
  FROM myfile a JOIN history b ON a.field1 = b.field1

I think just doing a LEFT OUTER JOIN would do it as long as you COALESCE the desired fields in your select list.  Give me a second, and I'll whip you up an example.

HTH,
DaveSlash

outer joins are "heavier" than inner joins

but I agree it could probably work

SELECT a.field1, isnull(b.shpdt, c.shpdt) shpdt
  FROM myfile a
      LEFT OUTER JOIN current b
          ON a.field1 = b.field1
      LEFT OUTER JOIN history c
          ON a.field1 = c.field1

this, again, assumes field1 isn't in both tables though, if it is then add the IN clause to the history join as shown in the first option.


as with any performance claims (or really, any claim)  test for yourself to determine which performs best.

an example as promised ....

create table d1 (
   theKey integer
);

insert into d1
values (1);

insert into d1
values (2);

insert into d1
values (3);

insert into d1
values (4);

create table d2 (
  theKey integer,      
  someStuff varchar(10)
);

create table d3 (
  theKey integer,      
  someStuff varchar(10)
);

insert into d2
values(1, 'one');

insert into d3
values(2, 'two');

insert into d2
values(3, 'three');

insert into d3
values(4, 'four');

select d1.theKey,
       coalesce(d2.someStuff, d3.someStuff) someStuff
  from d1
  left outer join d2
    on d2.theKey = d1.theKey
  left outer join d3
    on d3.theKey = d1.theKey;

THEKEY   SOMESTUFF
     1   one      
     2   two      
     3   three    
     4   four

Select allOpen in new window

http:#37228027 and  http:#37228093 do essentially the same thing

the second one changed column names and used coalesce instead of isnull

 and,as such has the same caveats apply to both.  if the field1(theKey) column has the same value in both the current(d2) and history(d3) tables then you'll get duplicated rows with the current(d2) values showing up multiple times

a distinct could be applied to either to remove the duplicates after they have been applied.

and, if neither table has a match, you'll probably want to apply a filter to eliminate any rows with nulls after joining

the inner join versions don't have that problem though

SELECT
    a.field1, COALESCE(c.shpdt, h.shpdt) AS ShpDt
FROM myfile a
LEFT OUTER JOIN current c ON
    c.field1 = a.field1
LEFT OUTER JOIN history h ON
    h.field1 = a.field1 AND c.field1 IS NULL

Hi,

Can the same value for field1 be in both files?

Thank you very much!  This works very well!