Cannot get Left JOIN to work correctly

  • I have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner join

    SELECT [DealerCode]

    ,iis.[ForecastSegmentId]

    ,fs.ForecastSegmentId

    ,[Name]

    FROM [dbo].[Sales] iis

    RIGHT OUTER JOIN dbo.Forecast fs ON fs.ForecastId = iis.ForecastId

    WHERE dealercode = '11'

    GROUP BY DealerCode,iis.ForecastId,fs.ForecastId,Name

    And I come out with all the rows from the 1st table (Sales).

    DealerCodeForecastIdForecastIdName

    001144Dual

    001155Mortgage

    001166Retail

    But in the 2nd table have 3 more records (7, 8, and 9).

    Where are those???

    I tried this with Left and Right Joins as well as Full.

    Still didn't get the extra records.

    This should be pretty straight forward.

    Any ideas what I am missing???

    Thanks,

    Tom

  • Actually, the topic should say RIGHT JOIN and not LEFT JOIN.

  • I assume, DealerCode belongs to table [dbo].[Sales].

    SELECT [DealerCode]

    ,iis.[ForecastSegmentId]

    ,fs.ForecastSegmentId

    ,[Name]

    FROM [dbo].[Sales] iis

    RIGHT OUTER JOIN dbo.Forecast fs ON fs.ForecastId = iis.ForecastId and iis.dealercode = '11'

    GROUP BY iis.DealerCode,iis.ForecastId,fs.ForecastId,Name

    _____________
    Code for TallyGenerator

  • tshad (8/20/2012)


    I have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner join

    SELECT [DealerCode]

    ,iis.[ForecastSegmentId]

    ,fs.ForecastSegmentId

    ,[Name]

    FROM [dbo].[Sales] iis

    RIGHT OUTER JOIN dbo.Forecast fs ON fs.ForecastId = iis.ForecastId

    WHERE dealercode = '11'

    GROUP BY DealerCode,iis.ForecastId,fs.ForecastId,Name

    And I come out with all the rows from the 1st table (Sales).

    DealerCodeForecastIdForecastIdName

    001144Dual

    001155Mortgage

    001166Retail

    But in the 2nd table have 3 more records (7, 8, and 9).

    Where are those???

    I tried this with Left and Right Joins as well as Full.

    Still didn't get the extra records.

    This should be pretty straight forward.

    Any ideas what I am missing???

    Thanks,

    Tom

    Hi Tom,

    Can you please provide DDL and sample records of the tables ?

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • Actually, this is a stripped down version of the actual query I was using so it is a little difficult to do that.

    But I did figure out my issue, just not sure how to deal with it.

    The where clause is causing the problem. The Join is done fine but the the where clause filters out anything that doesn't have DealerCode ="11".

    The lines that have the null for a DealerCode, which the extra lines would have, were also filtered out.

    I needed to change Where clause to:

    WHERE dealercode = '11' OR dealerCode IS NULL.

    That works fine with one dealercode but if I have multiple dealercodes, it doesn't work as I get 3 records even if another dealercode is missing others so I can't tell which dealercode the extra records belong to.

    Thanks,

    Tom

  • tshad (8/20/2012)


    Actually, this is a stripped down version of the actual query I was using so it is a little difficult to do that.

    But I did figure out my issue, just not sure how to deal with it.

    The where clause is causing the problem. The Join is done fine but the the where clause filters out anything that doesn't have DealerCode ="11".

    The lines that have the null for a DealerCode, which the extra lines would have, were also filtered out.

    I needed to change Where clause to:

    WHERE dealercode = '11' OR dealerCode IS NULL.

    That works fine with one dealercode but if I have multiple dealercodes, it doesn't work as I get 3 records even if another dealercode is missing others so I can't tell which dealercode the extra records belong to.

    Thanks,

    Tom

    Put this, dealercode = '11' , in with the join criteria for the right outer join instead of in the WHERE clause. Let us know how that works.

  • Ok..so if am not wrong then dealer code is in the first table and you are looking for all the records in second table.

    If this is the case then I am confused by your statement.

    As the extra records do not belongs to any dealer code these are records(having dealer code as NULL) which do not found any match in first table.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • I made some changes and created a couple of tables to show the issue.

    Either way (change where clause or Join with the test for the DealerCode) solves the first issue. Here is the schema and inserts as well as the queries. These tables are just quick and dirty for illustrations only.

    CREATE TABLE [dbo].[Sales](

    [SalesID] [int] NULL,

    [DealerCode] [varchar](15) NULL,

    [ForecastID] [int] NULL

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[Forecast](

    [ForecastId] [int] NULL,

    [Name] [varchar](50) NULL

    ) ON [PRIMARY]

    INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(1, '11', 4)

    INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(2, '11', 5)

    INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(3, '11', 6)

    INSERT Forecast(ForecastID, Name) VALUES(4, 'Dual')

    INSERT Forecast(ForecastID, Name) VALUES(5, 'Mortgage')

    INSERT Forecast(ForecastID, Name) VALUES(6, 'Retail')

    INSERT Forecast(ForecastID, Name) VALUES(7, 'Wholesale')

    INSERT Forecast(ForecastID, Name) VALUES(8, 'Region')

    INSERT Forecast(ForecastID, Name) VALUES(9, 'Market')

    SELECT DealerCode

    ,s.ForecastId

    ,fc.ForecastId

    ,Name

    FROM [Sales] s

    RIGHT OUTER JOIN dbo.Forecast fc ON fc.ForecastId = s.ForecastId

    WHERE dealercode = '11' OR dealerCode IS NULL

    GROUP BY DealerCode,s.ForecastId,fc.ForecastId,Name

    ORDER BY DealerCode

    Results:

    DealerCodeForecastIdForecastIdName

    NULLNULL7Wholesale

    NULLNULL8Region

    NULLNULL9Market

    1144Dual

    1155Mortgage

    1166Retail

    But now if I add a few more records with a couple more Dealers, I don’t get the results I am looking for:

    INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(1, '5', 4)

    INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(2, '5', 5)

    INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(3, '5', 6)

    INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(1, '5', 7)

    INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(2, '5', 8)

    INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(3, '8', 4)

    The new query:

    SELECT DealerCode

    ,s.ForecastId

    ,fc.ForecastId

    ,Name

    FROM [Sales] s

    RIGHT OUTER JOIN dbo.Forecast fc ON fc.ForecastId = s.ForecastId

    WHERE dealercode in ('5','8','11') OR dealerCode IS NULL

    GROUP BY DealerCode,s.ForecastId,fc.ForecastId,Name

    ORDER BY DealerCode

    And the results:

    DealerCodeForecastIdForecastIdName

    NULLNULL9Market

    1144Dual

    1155Mortgage

    1166Retail

    544Dual

    555Mortgage

    566Retail

    577Wholesale

    588Region

    844Dual

    Here I have 1 null record for the whole result set but I want to show what each dealers forecast records are as well as the forecast records that each dealer doesn’t have.

    So Dealer 11 should have 3 null records as before. Dealer 5 should have 1 null and Dealer 8 should have 4 null records and have them together with the dealer.

    Something like:

    DealerCodeForecastIdForecastIdName

    NULLNULL7Wholesale

    NULLNULL8Region

    NULLNULL9Market

    1144Dual

    1155Mortgage

    1166Retail

    NULLNULL9Market

    544Dual

    555Mortgage

    566Retail

    577Wholesale

    588Region

    NULLNULL7Wholesale

    NULLNULL8Region

    NULLNULL9Market

    NULLNULL8Region

    844Dual

    Thanks,

    Tom

  • The closest I can come is this: -

    SELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.Name

    FROM [dbo].[Forecast] fc

    OUTER APPLY (SELECT code

    FROM (VALUES('5'),('8'),('11'))a(code)

    ) c

    LEFT OUTER JOIN [dbo].[Sales] s ON s.DealerCode = c.code AND s.ForecastID = fc.ForecastID

    ORDER BY fc.Name;

    I don't fully understand your requirements. If what you want is to have 1 record per dealer whether or not they have a forecast and you're filtering to three dealer codes then surely the most you can have is 3 records per forecast?

    Here's the sample data I used (edited to make it easier to read for me): -

    SET NOCOUNT ON;

    --Create tables

    CREATE TABLE [dbo].[Sales] ([SalesID] [int] NULL, [DealerCode] [varchar](15) NULL, [ForecastID] [int] NULL) ON [PRIMARY];

    CREATE TABLE [dbo].[Forecast] ([ForecastId] [int] NULL, [Name] [varchar](50) NULL) ON [PRIMARY];

    --Insert sample data

    INSERT INTO [dbo].[Sales]

    SELECT SalesID, DealerCode, ForecastID

    FROM (VALUES(1, '11', 4),(2, '11', 5),(3, '11', 6),(1, '5', 4),

    (2, '5', 5),(3, '5', 6),(1, '5', 7),(2, '5', 8),

    (3, '8', 4)

    )a(SalesID, DealerCode, ForecastID);

    INSERT INTO [dbo].[Forecast]

    SELECT ForecastID, Name

    FROM (VALUES(4, 'Dual'),(5, 'Mortgage'),(6, 'Retail'),(7, 'Wholesale'),

    (8, 'Region'),(9, 'Market')

    )a(ForecastID, Name);

    --Expected result

    SELECT DealerCode, SalesForecastId, ForecastForecastId, Name

    FROM (VALUES(NULL,NULL,7,'Wholesale'),(NULL,NULL,8,'Region'),(NULL,NULL,9,'Market'),

    (11,4,4,'Dual'),(11,5,5,'Mortgage'),(11,6,6,'Retail'),(NULL,NULL,9,'Market'),

    (5,4,4,'Dual'),(5,5,5,'Mortgage'),(5,6,6,'Retail'),(5,7,7,'Wholesale'),

    (5,8,8,'Region'),(NULL,NULL,7,'Wholesale'),(NULL,NULL,8,'Region'),

    (NULL,NULL,9,'Market'),(NULL,NULL,8,'Region'),(8,4,4,'Dual')

    )a(DealerCode, SalesForecastId, ForecastForecastId, Name)

    ORDER BY Name;

    Here's my attempt: -

    --Actual Query

    SELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.Name

    FROM [dbo].[Forecast] fc

    OUTER APPLY (SELECT code

    FROM (VALUES('5'),('8'),('11'))a(code)

    ) c

    LEFT OUTER JOIN [dbo].[Sales] s ON s.DealerCode = c.code AND s.ForecastID = fc.ForecastID

    ORDER BY fc.Name;

    Results in: -

    DealerCode ForecastID ForecastId Name

    --------------- ----------- ----------- --------------------------------------------------

    5 4 4 Dual

    8 4 4 Dual

    11 4 4 Dual

    NULL NULL 9 Market

    NULL NULL 9 Market

    NULL NULL 9 Market

    5 5 5 Mortgage

    NULL NULL 5 Mortgage

    11 5 5 Mortgage

    5 8 8 Region

    NULL NULL 8 Region

    NULL NULL 8 Region

    5 6 6 Retail

    NULL NULL 6 Retail

    11 6 6 Retail

    5 7 7 Wholesale

    NULL NULL 7 Wholesale

    NULL NULL 7 Wholesale

    But you have your expected result as : -

    DealerCode SalesForecastId ForecastForecastId Name

    ----------- --------------- ------------------ ---------

    5 4 4 Dual

    11 4 4 Dual

    8 4 4 Dual

    NULL NULL 9 Market

    NULL NULL 9 Market

    NULL NULL 9 Market

    5 5 5 Mortgage

    11 5 5 Mortgage

    NULL NULL 8 Region

    NULL NULL 8 Region

    5 8 8 Region

    NULL NULL 8 Region

    5 6 6 Retail

    11 6 6 Retail

    NULL NULL 7 Wholesale

    5 7 7 Wholesale

    NULL NULL 7 Wholesale

    If you can explain the expected result, I'm sure that someone will be able to help.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • SELECT *

    FROM

    (SELECT DealerCode FROM Sales GROUP BY DealerCode) m

    CROSS JOIN Forecast fc

    LEFT JOIN [Sales] s on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId

    ORDER BY m.DealerCode

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • That worked really good.

    But I couldn't get it to work with a many to many table in the middle

    Here is the schema:

    CREATE TABLE [dbo].[Dealers](

    [DealerID] [int] NULL,

    [DealerCode] [varchar](15) NOT NULL,

    [DealerName] [varchar](50) NOT NULL

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[Forecast](

    [ForecastId] [int] NULL,

    [Name] [varchar](50) NULL

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[DealerForecastMap](

    [DealerForecastMapID] [int] NULL,

    [DealerID] [varchar](15) NULL,

    [ForecastID] [int] NULL

    ) ON [PRIMARY]

    INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(1, '11', 'Sears')

    INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(2, '05', 'Pick And Save')

    INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(3, '08', 'Pennys')

    INSERT Forecast(ForecastID, Name) VALUES(4, 'Dual')

    INSERT Forecast(ForecastID, Name) VALUES(5, 'Mortgage')

    INSERT Forecast(ForecastID, Name) VALUES(6, 'Retail')

    INSERT Forecast(ForecastID, Name) VALUES(7, 'Wholesale')

    INSERT Forecast(ForecastID, Name) VALUES(8, 'Region')

    INSERT Forecast(ForecastID, Name) VALUES(9, 'Market')

    INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(1, 1, 4)

    INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(2, 1, 5)

    INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(3, 1, 6)

    INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(4, 2, 4)

    INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(5, 2, 5)

    INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(6, 2, 6)

    INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(7, 2, 7)

    INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(8, 2, 8)

    INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(9, 3, 4)

    SELECT d.DealerID, d.DealerCode, DealerName, f.ForecastID, Name

    FROM Dealers d

    JOIN DealerForecastMap dfm

    ON d.DealerID = dfm.DealerID

    JOIN Forecast f

    ON f.ForecastId = dfm.ForecastID

    And the results:

    DealerIDDealerCodeDealerNameForecastIDName

    111Sears4Dual

    111Sears5Mortgage

    111Sears6Retail

    205Pick And Save4Dual

    205Pick And Save5Mortgage

    205Pick And Save6Retail

    205Pick And Save7Wholesale

    205Pick And Save8Region

    308Pennys4Dual

    How would I show the results with each dealer showing all the Forecasts including the forecasts each one is missing?

    Thanks,

    Tom

  • The same way we've already explained.

    SELECT *

    FROM (SELECT DealerID, DealerCode, DealerName, ForecastId, Name

    FROM Dealers d

    CROSS JOIN Forecast f) df

    LEFT OUTER JOIN DealerForecastMap m ON df.ForecastId = m.ForecastId AND df.DealerID = m.DealerID;

    Results in: -

    DealerID DealerCode DealerName ForecastId Name DealerForecastMapID DealerID ForecastId

    ----------- --------------- -------------------------------------------------- ----------- -------------------------------------------------- ------------------- --------------- -----------

    1 11 Sears 4 Dual 1 1 4

    1 11 Sears 5 Mortgage 2 1 5

    1 11 Sears 6 Retail 3 1 6

    1 11 Sears 7 Wholesale NULL NULL NULL

    1 11 Sears 8 Region NULL NULL NULL

    1 11 Sears 9 Market NULL NULL NULL

    2 05 Pick And Save 4 Dual 4 2 4

    2 05 Pick And Save 5 Mortgage 5 2 5

    2 05 Pick And Save 6 Retail 6 2 6

    2 05 Pick And Save 7 Wholesale 7 2 7

    2 05 Pick And Save 8 Region 8 2 8

    2 05 Pick And Save 9 Market NULL NULL NULL

    3 08 Pennys 4 Dual 9 3 4

    3 08 Pennys 5 Mortgage NULL NULL NULL

    3 08 Pennys 6 Retail NULL NULL NULL

    3 08 Pennys 7 Wholesale NULL NULL NULL

    3 08 Pennys 8 Region NULL NULL NULL

    3 08 Pennys 9 Market NULL NULL NULL


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Actually, it isn't exactly the same way.

    I was trying to figure out how to get your way to work with a middle table and couldn't get it to work.

    You're way works but it is different.

    In the query you have the CROSS JOIN in the Derived Table (df)

    SELECT *

    FROM (SELECT DealerID, DealerCode, DealerName, ForecastId, Name

    FROM Dealers d

    CROSS JOIN Forecast f) df

    LEFT OUTER JOIN DealerForecastMap m

    ON df.ForecastId = m.ForecastId AND df.DealerID = m.DealerID;

    In the old query, you have the CROSS JOIN outside of the Derived table (m).

    SELECT *

    FROM

    (SELECT DealerCode FROM Sales GROUP BY DealerCode) m

    CROSS JOIN Forecast fc

    LEFT JOIN [Sales] s

    on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId

    ORDER BY m.DealerCode

    I am not sure of the difference but I do know I could not seem to get my query with the middle table to work as it was set up for my other example that had just 2 tables.

    Not sure why yet, but will look at it more closely.

    Thanks,

    Tom

  • tshad (8/23/2012)


    Actually, it isn't exactly the same way.

    I was trying to figure out how to get your way to work with a middle table and couldn't get it to work.

    You're way works but it is different.

    In the query you have the CROSS JOIN in the Derived Table (df)

    SELECT *

    FROM (SELECT DealerID, DealerCode, DealerName, ForecastId, Name

    FROM Dealers d

    CROSS JOIN Forecast f) df

    LEFT OUTER JOIN DealerForecastMap m

    ON df.ForecastId = m.ForecastId AND df.DealerID = m.DealerID;

    In the old query, you have the CROSS JOIN outside of the Derived table (m).

    SELECT *

    FROM

    (SELECT DealerCode FROM Sales GROUP BY DealerCode) m

    CROSS JOIN Forecast fc

    LEFT JOIN [Sales] s

    on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId

    ORDER BY m.DealerCode

    I am not sure of the difference but I do know I could not seem to get my query with the middle table to work as it was set up for my other example that had just 2 tables.

    Not sure why yet, but will look at it more closely.

    Thanks,

    Tom

    Hi Tom

    In both queries the intent is to build an intermediate table which is a cross of DealerID and ForecastID, to which you can LEFT JOIN other tables. This ensures that you're starting with the whole picture - every dealer has every forecast.

    The structural difference between the two queries is to accommodate the changes in the sample tables and data you've provided.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • tshad (8/23/2012)


    Actually, it isn't exactly the same way.

    I was trying to figure out how to get your way to work with a middle table and couldn't get it to work.

    You're way works but it is different.

    You've got a little defensive there and having re-read what I wrote I can see why. I hadn't intended what I wrote to sound like I was accusing you of not paying attention, I simply wanted to draw your attention to the fact that the logic behind the idea is identical in both your previous question and your new question. As Chris explained (far better than I), the difference in the structure of the query is to accommodate the changes to the schema that you supplied with your question.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply