Geek Answers Handbook

SQL Join Supplement? Need T-SQL Help

I will try to make my question sound as unconfusing as possible. I declare any errors in the wording in advance, as I try to formulate my question as best as possible:

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 operator (or continuation of the first join) that returns all the results from table A that do NOT have a match in table B, but in this second set of results I need one of the columns to be set to "N / A" to identify records that did not match.

In other words, I need something that will return everything to table A, but will also identify rows that were not matched in B. This information is then used in the report.

Here is what I still have:

I have the first part:

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

It gives me matching entries and only matching entries

I tried to add this second connection:

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

Hoping this leads to a record that doesn't match me (I then planned to use REPLACE in the column of interest to label this column “N / A”), but there is something clearly wrong with my syntax, as it throws exceptions.

- , . , , . , , , , .

, . .

!

: , , , , , .

+5
3

:

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

:

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

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

:

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

, . LEFT JOIN, , , ...

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

, 3 ?

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
+3

Try using union:

select A.id, b.id, b.desc from tablea A LEFT OUTER JOIN dbo.chart B
ON B.UserName = A.user_name

UNION 

select a.id, 0, 'N/A' from tablea where NOT EXISTS (select * from B.UserName = A.user_name)
0
source

More articles:


All Articles