Join multiple files with SQL

I have 2 files ( Current & History ) that I would like to join in my sql.

I 'm not sure which file the records are in. I would like to check the current file first if the records are not present then i would like to check the history. I've no idea how to place logic around the joins. Something like this.....

Select a.field1, b.shpdt from myfile a
IF
join current b on a.field1 = b.field1
ELSE
join history b on a.field1 = b.field1
ENDIF

Is something like this even possible?
CleavisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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)
0
sdstuberCommented:
or,  combine the current and history first, then join to that


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


this assumes the current and history data is mutually exclusive  that is, you'll never have the same field1 in both tables
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

sdstuberCommented:
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
0
Dave FordSoftware Developer / Database AdministratorCommented:

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
0
sdstuberCommented:
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.

0
Dave FordSoftware Developer / Database AdministratorCommented:
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

Open in new window

0
sdstuberCommented:
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
0
Scott PletcherSenior DBACommented:
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
0
MurpheyApplication ConsultantCommented:
Hi,

Can the same value for field1 be in both files?
0
CleavisAuthor Commented:
Thank you very much!  This works very well!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.