CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Deep Sharma
Deep Sharma

Reputation: 3483

show null values that does not match with another table sql server

I have two table "Table A" and "Table B"

Table A is a result of joins with other tables. And Table B is a separate table with having 1 field common in Table A.

Table A:

Year   Name  Value
2011   A     Item1
2010   B      1
2011   C     Item2

Table B:

id   Value
1    Item1
2    Item2
3    Item3
4    Item4

I want result to be like:

Year   Name  Value
2011   A     Item1
2010   B      NULL
2011   C     Item2

My Efforts are:

SELECT d.Portfolio,
       d.Name,
       d.AccountName,
       d.CashGAAP,
       d.OriginalDate,
       d.Amount,
       d.AccountNumber,
       d.AttributeSetName,
       d.TheDate,
       d.Year,
       d.Value
FROM (SELECT Portfolio.LegalEntityName AS Portfolio,
             Forecast.Name,
             CoA.AccountName,
             Forecast_Data.CashGAAP,
             CONVERT(DATE, Forecast_Data.TheDate) AS OriginalDate,
             SUM(Forecast_Data.Amount) AS Amount,
             CoA.AccountNumber ,
             Attribute_Set.AttributeSetName,
             '' + CONVERT(VARCHAR, YEAR(Forecast_Data.TheDate)) + '-'
             + CONVERT(VARCHAR, MONTH(Forecast_Data.TheDate)) + '-01' AS TheDate,
             YEAR(Forecast_Data.TheDate) AS Year,
             Forecast_Attribute.Value
FROM Portfolio
     INNER JOIN Forecast ON Portfolio.PortfolioID = Forecast.PortfolioID
     INNER JOIN Forecast_Account 
        ON Forecast.ForecastID = Forecast_Account.ForecastID
     INNER JOIN Forecast_Data 
        ON Forecast_Account.ForecastAccountID = 
              Forecast_Data.ForecastAccountID
     INNER JOIN CoA ON CoA.AccountNumber = Forecast_Account.AccountNumber
     INNER JOIN Attribute_Set 
        ON CoA.AttributeSetID = Attribute_Set.AttributeSetID
     INNER JOIN Forecast_Attribute 
        ON Forecast_Account.ForecastAccountID = 
              Forecast_Attribute.ForecastAccountID
     WHERE (Forecast.ForecastID = 5)
        OR (Forecast.ForecastID = 6)
     GROUP BY Portfolio.LegalEntityName,
              Forecast.Name,
              CoA.AccountName,
              Forecast_Data.CashGAAP,
              Forecast_Data.TheDate,
              CoA.AccountNumber,
              Attribute_Set.AttributeSetName,
              Forecast_Attribute.Value) 
     AS d
     LEFT OUTER JOIN Vendor ON d.Value = Vendor.VendorName

I have renamed the example tables that are explained in Question: 

Table A = d
Table B = Vendor

Upvotes: 2

Views: 5476

Answers (3)

Suraj Singh
Suraj Singh

Reputation: 4069

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables

You need to apply LEFT JOIN

SELECT column_name(s)
FROM TableA
LEFT OUTER JOIN TableB
ON tableA.Value=tableB.Value;

The LEFT JOIN keyword returns all rows from the left table (tableA), with the matching rows in the right table (tableB). The result is NULL in the right side when there is no match.

Upvotes: 1

Walter Mitty
Walter Mitty

Reputation: 18940

select
    A.Year, A.Name, B.Value
from
    A left join B on A.Value= B.value;

Upvotes: 1

valex
valex

Reputation: 24144

You should LEFT JOIN B to the A and show B.Value instead of A.Value in the SELECT list:

SELECT Year, Name, B.Value
FROM A 
LEFT JOIN B on A.Value=B.Value

Upvotes: 7

Related Questions