SQL Sever 2010 View Join Does Not Return All Rows

For starters...

• There is no version of SQL Server named 2010.

• Please insert your code into a CODE block (see toolbar, third button from the right) for easier readability.

>Not every line number appears for each customer in the tblSSResultsByReportGroupYear table.  I need to have all the line numbers appear
LEFT JOIN means your query will return all rows in the table to the left of the LEFT JOIN, and will only populate values from the table on the right side of the LEFT JOIN.  And it appears you have those tables reversed.  So try this..

SELECT l.LineNumber, l.LineName, rryg.ParishNumber, rryg.YearLongTitle
FROM dbo.tblSSResultsByReportYearGroup rryg   
	LEFT JOIN dbo.tblSSLine l ON rryg.GroupNumber = l.GroupNumber 

Select allOpen in new window

Note that I cleaned up this query with table aliases and indentation for easier readability.

You'll also want to spell out if there any rows in line not in ResultsByReportYearGroup, and vice versa, as I don't see a data mockup in this question so it's hard to tell.  Either way, do a Google search for 'SQL JOIN' and you'll see all sorts of Venn diagrams with T-SQL code to demonstrate every scenario.

Use dbo.tblSSLine FULL JOIN to show every row from both tables. Here the missing info will show up as null. Then, in the where clause using IsNot Null eliminate you don't want to display.

Thanks for the quick response.  I haven't used this site in years so sorry I didn't know to post the code into the code block.  
I still get the same results.  The result still misses rows where there isn't a dollar amount in the rryg table.  Thanks!

After changing the join to dbo.tblSSLine FULL JOIN, us WHERE last_name IS NOT NULL, for example to ignore null last_names. Your columns will be something different than last_name. Change it accordingly.

Mike,

I'm not sure I understand.  I want to include all rows even if some of the values are null.

Thanks!

>I want to include all rows even if some of the values are null.
Give us a data mockup of how the table exists in both tables, and how you wish the return set to appear.
I'm guessing there's things going on in your data that haven't been stated in this question, and we hate to guess.

the L table has a list of rows for line numbers in a report.  For example:  
Line 1 - General Income,
Line 2- Lease Income,
Line 3, Fundraising Income.

The rryg table has financial results for each customer.  Some customers do not have Lease Income therefore there is not a row in the rryg table.  My goals is to display each of the line numbers and a zero on Line 2 if the customer did not have Lease income.  

Let me know if there is a better way to show the table design to you.

This is the L table

SSLineID                int                              Unchecked
LineNumber              numeric(18, 0)      Checked
GroupNumber      numeric(18, 0)      Checked
LineName               varchar(50)              Checked

This is the rryg table:
ResultsByYearID      int      Unchecked
CustomerNumber      numeric(10, 0)      Checked
YearNumber      numeric(18, 0)      Checked
YearShortTitle      varchar(50)      Checked
GroupNumber      numeric(18, 0)      Checked
TotalAmount      money      Checked

Sorry I hit send to fast.  The two tables are joined on the GroupNumber field.  I'm expecting to get all of the LineNumber fields from the L table in the results.

Thanks!

USE [PFS]
GO

/****** Object:  Table [dbo].[tblSSLine]    Script Date: 3/27/2017 8:42:39 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblSSLine](
	[SSLineID] [int] IDENTITY(1,1) NOT NULL,
	[LineNumber] [numeric](18, 0) NULL,
	[Multiplier] [numeric](18, 0) NULL,
	[GroupNumber] [numeric](18, 0) NULL,
	[LineName] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Select allOpen in new window

USE [PFS]
GO

/****** Object:  Table [dbo].[tblSSResultsByReportYearGroup]    Script Date: 3/27/2017 8:44:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblSSResultsByReportYearGroup](
	[ResultsByYearID] [int] IDENTITY(1,1) NOT NULL,
	[ParishNumber] [numeric](10, 0) NULL,
	[YearNumber] [numeric](18, 0) NULL,
	[YearShortTitle] [varchar](50) NULL,
	[YearLongTitle] [varchar](50) NULL,
	[GroupNumber] [numeric](18, 0) NULL,
	[GroupName] [varchar](50) NULL,
	[TotalAmount] [money] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Select allOpen in new window

Data in tblSSLine:

SSLineID         LineNumber      Multiplier      GroupNumber      LineName
1                                  11            1                       11                      Operating Revenue
2                                  12            1                         12                      Rental Income
3                                  13              1                        13                      Fundraising

Data in the tblSSResultsByReportYearGroup table

ParishNumber      YearNumber      YearShortTitle      YearLongTitle      GroupNumber      GroupName                            TotalAmount
1                                       1                   2011                              2010-2011      11                              Operating revenue            $9,588,306.22
1                                       1                   2011                              2010-2011      13                               Fundraising                               $40,968.70
1                                       1                   2011                              2010-2011      14                               Extraordinary/Bequests      $2,779,495.00

As you can see for ParishNumber 1 there is no record for Group #12 but there is a group # 12 (Rental Income) in the tblSSLine table.

When I use the outer join Group 12 is not in the result.

I hope this isn't too long and I really appreciate your help.

Andy

Thanks for the help.  I made a change to the original table design to resolve the issue.