CODEWITHSUNDEEP
sql-servert-sql
JsonStatham
JsonStatham

Reputation: 10364

Join where records dont match

I need to do a join on two tables, I need EVERYTHING from table A, and just the stuff from table B where the 'REF' for each row, is not already in table A's results.

Key Facts: Table B contains the complete amount of ID's/Names however all the other information is blank. Its basically just a table with all employee's name's and id's but its a complete list. Table A contains a limited amount of results but all the other columns have data in them.

What I need is to use Table B as a complete reference rather than just see what exists in table A so basically:

"Show me everything from table A, and add the extra people's details found in table B where they dont already exist in table A to give me a complete result set"

select
ID,
Name,
StartDate,
EndDate,
State,
Status,
Comment,
IsComment
from
tableA


select
ID,
Name,
StartDate,
EndDate,
State,
Status,
Comment,
IsComment
from
tableB

Table A contents:

ID       Name     START_DATE       END_DATE      STATE    Status     Comment    Is_Comment
6760    chris       2012-09-03      2012-09-09   4       Applied                   0
6524     dave     2012-09-03        2012-09-09    4        Applied                 0
4268     james    2012-09-03        2012-09-09    4        Applied    Friday-Off   1
7851     rob      2012-09-03        2012-09-09    4       Applied                  0

Table B contents

    ID        Name     START_DATE       END_DATE      STATE    Status     Comment    Is_Comment
    6760    Chris   
    6524    dave    
    4268    james   
    7851    rob      
    4521    ryan
    5698   julie
    4596    rory
    1111     mary
    5621     owain
    9999     jacob

After the join what I want to see:

    ID       Name     START_DATE       END_DATE      STATE    Status     Comment    Is_Comment

    6760    chris       2012-09-03      2012-09-09   4       Applied                   0
    6524     dave     2012-09-03        2012-09-09    4        Applied                 0
    4268     james    2012-09-03        2012-09-09    4        Applied    Friday-Off   1
    7851     rob      2012-09-03        2012-09-09    4       Applied                  0   
   4521    ryan
   5698    julie
   4596    rory
  1111     mary
  5621     owain
  9999     jacob

Upvotes: 1

Views: 123

Answers (3)

András Ottó
András Ottó

Reputation: 7695

Try this:

select
tableB.ID,
tableB.Name,
tableA.StartDate,
tableA.EndDate,
tableA.State,
tableA.Status,
tableA.Comment,
tableA.IsComment
from
tableB
LEFT JOIN tableA on tableB.ID = tableA.ID

Because then every ID and Name will be listed from tableB, and every other column is listed from tableA. If there is no connection between the tables, then the other columns got the NULL from TableA - because of LEFT JOIN -, if there is a connection then you get the filled values from TableA also.

Here is an SQL fiddle how this simple solution works.

Upvotes: 2

Deepak.Aggrawal
Deepak.Aggrawal

Reputation: 1277

Please use this SQL:

declare @tableA table 
(
ID int,
Name nvarchar(250),
Age int
)

declare @tableB table 
(
ID int,
Name nvarchar(250),
Age int
)

Insert @tableA values (1,'a',10);
Insert @tableA values (2,'b',20);

Insert @tableB values (1,'a',null);
Insert @tableB values (2,'b',null);
Insert @tableB values (3,'c',null);
Insert @tableB values (4,'d',null);

select  tblResult.*,T1.Age from 
(
select ID,Name from @tableA
union
select ID,Name from @tableB) as tblResult 
left join @tableA as T1 on tblResult.ID =T1.ID

Upvotes: 1

Hassan
Hassan

Reputation: 1433

Use a Left join

SELECT [columnsListYouNeed]
FROM  TableB 
LEFT JOIN TableA ON TableA.ID = TableB.ID

Upvotes: 0

Related Questions