Complement of a SQL Join? T-SQL help needed

Question

I will try to make my question sound as unconfusing as possible. I appologize in advance for any wording mistakes as I try to phrase my question as best as I can:

Using T-SQL I need to write a join statement that gets me all the results that have a match in table A and table B

AND (!)

another join statement (or a continuation of the first join) that returns all the results from table A that DID NOT have a match in table B, BUT in this second result set I need to have one of the columns set to "N/A" to identify the records that didn't have a match.

In other words, I need something that would return everything in table A but would also identify the rows that weren't matched in B. That information is then used in a report.

Here is what I have so far:

I have the first part done:

LEFT OUTER JOIN dbo.chart B
ON B.UserName = A.user_name

That gets me the matching records and just the matching records

I tried adding this second join:

JOIN dbo.chart
ON NOT EXISTS (select * from B.UserName = A.user_name)

Hoping it would get me the non-matching records (I was planning to then use REPLACE on the column of interest to label that column "N/A") but there is something clearly wrong with my synthax as that generates exceptions.

My question is what do I need to change to get me the results you need. I do know that I need to have at least one join as I have other part of the query to work with. I just don't know if I need to have that one join return both sets of data of I actually do need a second one just for the non-matching records.

Hope this wasn't too confusing. Any help would be greatly appreciated.

Thank you!

Update: I would just like to emphasize that the reason I considered using a second join instead of getting all of the results at once is because I need to correctly identify and label those rows that weren't matched within everything that I get back.

Answer 1

I'm not sure where you are going with the second join. LEFT JOIN seems to do everything you want...

DECLARE @tableA TABLE (a_id INT) INSERT INTO @tableA VALUES (1), (2), (3), (4)
DECLARE @tableB TABLE (b_id INT) INSERT INTO @tableB VALUES      (2), (3)

SELECT * FROM @tableA AS A LEFT JOIN @tableB AS B on A.a_id = b.b_id

 a_id | b_id
------+------
   1  | NULL
   2  |  2
   3  |  3
   4  | NULL

Unless you mean that you're actually joining 3 tables together?

DECLARE @org    TABLE (o_io INT) INSERT INTO @org    VALUE       (2), (3), (4)
DECLARE @tableA TABLE (a_id INT) INSERT INTO @tableA VALUES (1), (2), (3), (4)
DECLARE @tableB TABLE (b_id INT) INSERT INTO @tableB VALUES      (2), (3)

SELECT
  *
FROM
  @org        AS O
INNER JOIN
  @tableA     AS A
    ON O.o_id = A.a_id
LEFT JOIN
  @tableB     AS B
    ON A.a_id = b.b_id

 o_id | a_id | b_id
------+------+------
   2  |   2  |  2
   3  |   3  |  3
   4  |   4  | NULL

Answer 2

Sample data:

declare @TableA table
(
  TableAID int,
  TableAName varchar(10)
)

declare @TableB table
(
  TableBID int,
  TableBName varchar(10),
  TableAID int
)

insert into @TableA values
(1, 'A 1'),
(2, 'A 2'),
(3, 'A 3')

insert into @TableB values
(1, 'B 1', 1),
(2, 'B 2', 2)

N/A instead of TableBName:

select A.TableAName,
       coalesce(B.TableBName, 'N/A') as TableBName
from @TableA as A
  left outer join @TableB as B
    on A.TableAID = B.TableAID

Result:

TableAName TableBName
---------- ----------
A 1        B 1
A 2        B 2
A 3        N/A

Extra column for N/A:

select A.TableAName,
       B.TableBName,
       case when B.TableBID is null 
         then 'N/A' 
         else '' 
       end as TableBPresent
from @TableA as A
  left outer join @TableB as B
    on A.TableAID = B.TableAID    

Result:

TableAName TableBName TableBPresent
---------- ---------- -------------
A 1        B 1        
A 2        B 2        
A 3        NULL       N/A