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

All Courses

From novice to tech pro — start learning today.