need help with a join

table 1 has a column called ID1
table 2 has two columns called ID1 & ID2


The join would naturally be ID1 = ID1, but my sequence of join needs to be this.

table1.id1 = table2.id1 (but if there is not a match then match on) table1.id1 = table2.id2

is that possible..... to match on one column but if there is no match to try to match to another column?
jamesmetcalf74Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can join on both:
SELECT col1, col2, ..., colN
FROM table1 t1
    INNER JOIN table2 t2 ON t1.ID1=t2.ID1
    INNER JOIN table2 t3 ON t1.ID1=t3.ID2

Open in new window

ste5anSenior DeveloperCommented:
E.g.
 
DECLARE @T1 TABLE ( T1ID1 INT );

INSERT  INTO @T1
VALUES  ( 1 ),
        ( 2 ),
        ( 3 );

DECLARE @T2 TABLE ( T2ID1 INT, T2ID2 INT );

INSERT  INTO @T2
VALUES  ( 1, 1 ),
        ( 2, 3 ),
        ( 4, 3 );

WITH    Unioned
          AS ( SELECT   '1' AS Src ,
                        *
               FROM     @T1 T1
                        INNER JOIN @T2 T2 ON T1.T1ID1 = T2.T2ID1
               UNION ALL
               SELECT   '2' AS Src ,
                        *
               FROM     @T1 T1
                        INNER JOIN @T2 T2 ON T1.T1ID1 = T2.T2ID2
             ),
        Ordered
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY T1ID1 ORDER BY Src ) AS RN ,
                        *
               FROM     Unioned
             )
    SELECT  T1ID1 ,
            T2ID1 ,
            T2ID2
    FROM    Ordered
    WHERE   RN = 1;

Open in new window

Scott PletcherSenior DBACommented:
You can do it, but you have to use LEFT JOINs, and the second join needs to first verify that the first join didn't get a match.  For example:

SELECT t1.<col_name1>, t1.<col_name2>, ...,
    COALESCE(t2_ID1.<col_name11>, t2_ID2.<col_name11>) AS <col_name11>, ...
FROM table1 t1
LEFT OUTER JOIN table2 t2_ID1 ON t2_ID1.ID1 = t1.ID1
LEFT OUTER JOIN table2 t2_ID2 ON t2_ID1.ID1 IS NULL AND t2_ID2.ID2 = t1.ID2

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jamesmetcalf74Author Commented:
Thanks Gentlemen
ste5anSenior DeveloperCommented:
I don't agree with the split, cause every solution returns a different number of rows.. they can't be all correct.
Scott PletcherSenior DBACommented:
Can there even be multiple matches on t2.ID2??  If so, and you only want one row returned, you'd need to use OUTER APPLY instead of LEFT JOIN.  Add an ORDER BY if you want a specific row instead of just a random one (the UNION ALL method would also return a random row, btw).

SELECT t1.T1ID1, t2_ID1.T2ID1, t2_ID1.T2ID2, t2_ID2.T2ID1, t2_ID2.T2ID2
FROM @t1 t1
OUTER APPLY (
    SELECT TOP (1) *
    FROM @t2 t2
    WHERE t2.T2ID1 = t1.t1ID1
) AS t2_ID1
OUTER APPLY (
    SELECT TOP (1) *
    FROM @t2 t2
    WHERE t2_ID1.T2ID1 IS NULL AND t2.T2ID2 = t1.t1ID1
) AS t2_ID2
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

From novice to tech pro — start learning today.