Conditional SQL JOIN

I am trying to join on a table conditonally, so far I tried the following  but do not get the desired result

Select t1.Code, t1.c1, t1.c2, t2.c1
FROM table1 t1
INNER JOIN table2 t2 (nolock)
                              on t1.Code = t2.Code
                              AND (t2.Code = t1.Code or t2.Code IS NULL)

Also tried this
                              
Select t1.Code, t1.c1, t1.c2, t2.c1
FROM table1 t1
INNER JOIN table2 t2 (nolock)
                              on t1.Code = coalesce(t2.Code, t1.Code)
                              
Please note that table2 can have zero records, in that case I need to get all the records from table1
Bu when there are records in table2 , I need to get only those records in table2 which match the Code value in table1
LVL 1
countrymeisterAsked:
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.

Mike EghtebasDatabase and Application DeveloperCommented:
Select t1.Code, t1.c1, t1.c2, t2.c1 As T2C1
FROM table1 t1,  table2 t2
WHERE t1.Code = t2.Code;
0
dsackerContract ERP Admin/ConsultantCommented:
You want a LEFT JOIN:

Select t1.Code, t1.c1, t1.c2, t2.c1
FROM table1 t1
LEFT JOIN table2 t2 (nolock) on t2.Code = t1.Code
0
countrymeisterAuthor Commented:
dsacker,

if I do a left join I will get all the rows from table1, regardless of them being in table2 matching on the code.
I do not want that.
if table2 has rows I want the rows that match the code  value in table 2, hence the inner join.
0
dsackerContract ERP Admin/ConsultantCommented:
Perhaps what you want is this:
IF NOT EXISTS (SELECT 1 FROM table2 (nolock))
    SELECT t1.Code, t1.c1, t1.c2, NULL
    FROM table1 (nolock)
ELSE
    SELECT t1.Code, t1.c1, t1.c2, t2.c1
    FROM table1 t1
    INNER JOIN table2 t2 (nolock) on t2.Code = t1.Code

Open in new window

0
Scott PletcherSenior DBACommented:
Select t1.Code, t1.c1, t1.c2, t2.c1
FROM table1 t1
CROSS APPLY (
    SELECT TOP (1) *
    FROM (
        SELECT t2b.c1
        FROM table2 t2b
        WHERE
            t1.Code = t2b.Code
        UNION ALL
        SELECT NULL
        WHERE NOT EXISTS(SELECT TOP (1) * FROM table2)
    ) AS derived
) AS t2
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
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 2008

From novice to tech pro — start learning today.