How to join 2 tables with inner and left outer join

Question

I have two tables in MSSQL with the same structure T1 & T2. Both tables can INNER JOIN on Id but T2 may not contain the AccountId associated with the Id as in T1. .

T1   Id   AccountId   Name            T2      Id   AccountId   Name       
     111  5555        John                    111  5555        John 
     122  5555        David                   133  5555        Sharon   
     133  5555        Sharon                  

Below is the code I tried but the result is not working?. .

INSERT INTO T3
SELECT T1.Id,T1.AccountId,T1.Name
FROM T1
INNER JOIN T2 T2.Id = T1.id
LEFT OUTER JOIN T1.AccountId = T2.AccountId
WHERE AccountId = 5555

The expected result would be to insert values that are not in T1 into T3

Answer 1

This should be what you're after:

CREATE TABLE #T1 (id int, AccountId int, [Name] varchar(6));
CREATE TABLE #T2 (id int, AccountId int, [Name] varchar(6));
CREATE TABLE #T3 (id int, AccountId int, [Name] varchar(6));
INSERT INTO #T1
VALUES (111,5555,'John  '),
       (122,5555,'David '),
       (133,5555,'Sharon');
INSERT INTO #T2
VALUES (111,5555,'John  '),
       (133,5555,'Sharon');

INSERT INTO #T3 (id, AccountId, [Name])
SELECT T1.id,
       T1.AccountId,
       T1.[Name]
FROM #T1 T1
     LEFT JOIN #T2 T2 ON T1.ID = T2.id
WHERE T2.id IS NULL;

SELECT *
FROM #t3;

DROP TABLE #T1;
DROP TABLE #T2;
DROP TABLE #T3;

Answer 2

You need where clause:

INSERT INTO T3(Id, AccountId, Name)
    SELECT T1.Id, T1.AccountId, T1.Name
    FROM T1 LEFT JOIN
         T2
         ON T2.Id = T1.id
    WHERE T2.AccountId IS NOT NULL;

Notes:

• Only one LEFT JOIN is necessary. I don't know what the INNER JOIN is for.
• Every JOIN should be followed by an ON clause.
• You should list the columns when doing an INSERT.
• You need the WHERE to find non-matches.
• This query could also be written using NOT EXISTS.