Left Outer Join Query does not return all records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Left Outer Join Query does not return all records

Author

Topic

dan.ling@pcubed.com
Starting Member

4 Posts

Posted - 2008-11-20 : 22:12:12

Jeff and the SQL Team,

I’m new to SQL programming and have learned a lot on your blog – great job. I did not see how to post a question so I decided to email you directly/post here. I intend to use your dynamic crosstab code but I’m stuck on another issue with SQL that will eventually lead me to creating a crosstab query for a report. I’m working with MS Portfolio Server and Project Server data and just need to create some SSRS 2005 reports from SQL2005 data.

My issue is with a dataset created with a series of outer joins where I basically want to include all the records in “Table A” even if a corresponding record does not exist in “Table B” based on a key field. I want to get the null records in the dataset if a record is missing in table B. I’ve used a “LEFT OUTER JOIN” successfully for other queries but is does not seem to be working with the attached queries. Specifically, my issue is when I create a “LEFT OUTER JOIN” between Attributes and Attribute Values using the ID field in the Attribute List by Project query. The Attribute Values table does not have a record for all Attributes but I want the query to display the Null entries and I thought the “LEFT OUTER JOIN” would produce that result.

I’ve tried using AND, ISNULL, and IS Null in the SELECT and WHERE statements with no luck. I’m not sure about CROSS JOIN but I’m hoping you can point me to a solution. Feel free to use this in your blog if you have a solution that others can benefit from. Background INFO below and the TABLE Defintions are at the end. I can send you the DB also.

I thought this was a simple 2 table “LEFT OUTER JOIN” so its driving me nuts especially since it has worked with other queries.

Thanks.

The Query (I'm expecting one record for each attribute defined in sfATTRIBUTES that would include any NULL records from sfATTRIBUTE_VALUES)

SELECT sfATTRIBUTES.ID, sfATTRIBUTES.AccountID, sfATTRIBUTES.InternalClassID, sfATTRIBUTES.CategoryID, sfATTRIBUTES.EntityType,
sfATTRIBUTES.Name, sfATTRIBUTES.InternalName, sfATTRIBUTES.DataType, sfATTRIBUTES.ApplicationUsage, sfATTRIBUTES.IsCalculated,
sfATTRIBUTES.Formula, sfATTRIBUTES.MinNumericValue, sfATTRIBUTES.MinDateValue, sfATTRIBUTES.MaxNumericValue,
sfATTRIBUTES.MaxDateValue, sfATTRIBUTES.MaxStringLength, sfATTRIBUTES.Weight, sfATTRIBUTES.Mandatory, sfATTRIBUTES.CustomFormat,
sfATTRIBUTE_VALUES.EntityID, sfATTRIBUTE_VALUES.AttributeID, sfATTRIBUTE_VALUES.SolutionID, sfATTRIBUTE_VALUES.NumericValue,
sfATTRIBUTE_VALUES.CharValue, sfATTRIBUTE_VALUES.DateTimeValue, sfATTRIBUTE_VALUES.DefinitionID,
sfATTRIBUTE_VALUES.Structure1EntryID, sfATTRIBUTE_VALUES.Structure2EntryID
FROM sfATTRIBUTES INNER JOIN
sfATTRIBUTE_VALUES ON sfATTRIBUTES.ID = sfATTRIBUTE_VALUES.AttributeID
WHERE (sfATTRIBUTES.EntityType = N'Project') AND (sfATTRIBUTE_VALUES.Structure1EntryID = 26150)

Goal
Create a PPS SQL dataset for SSRS that contains a record for every available attribute by project. Ideally I would be filtering on project type attribute entities so the record set would not be huge considering the number of attributes PPS creates in the DB.

Example
My attribute dataset contains one record for each unique attribute filtered to include only project type attributes (150 records).
My project dataset contains one record for each defined structure filter to include only project type structures (10 records).
I want my dataset to contain 150 records for each project and include any null values so there should be 1500 total records.

Challenges
PPS only creates structure records when it needs to record an attribute value for a project, an attribute record won’t exist if a value is not needed by that project.
The attribute and project datasets are not a problem but I lose the null values when I join them.
I’ve set the query to return all rows on the attribute dataset but it still only returns the number of rows defined for the project in the project dataset.
I want to use only SSRS and SQL to create the reports and MOSS is available.
Minimize any hardcore SQL, .NET, ASP, XML, or other programming.

The Report Query
A crosstab query that pivots on the attribute name so I can use any of the attribute names as named parameters/fields in the report. The list of attribute names is dynamic and I don’t know if SSRS queries support the PIVOT and UNPIVOT functions. I also don’t know of any other method to get the attribute names to be available as report parameters/fields.

The Report (a form)
One or more formatted page(s) per project where I can place attributes on the page in any location

Table Definitions
USE [PPSAccountData]
GO
/****** Object: Table [dbo].[sfATTRIBUTES] Script Date: 11/21/2008 03:58:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sfATTRIBUTES](
[ID] [int] NOT NULL,
[AccountID] [int] NULL,
[InternalClassID] [int] NULL,
[CategoryID] [int] NULL,
[EntityType] [nvarchar](100) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[InternalName] [nvarchar](100) NULL,
[DataType] [int] NOT NULL,
[ApplicationUsage] [int] NOT NULL,
[IsCalculated] [int] NOT NULL CONSTRAINT [DF__sfATTRIBU__IsCal__22AA2996] DEFAULT ((0)),
[Formula] [nvarchar](4000) NULL,
[MinNumericValue] [float] NULL,
[MinDateValue] [datetime] NULL,
[MaxNumericValue] [float] NULL,
[MaxDateValue] [datetime] NULL,
[MaxStringLength] [int] NULL,
[Weight] [float] NULL,
[Mandatory] [int] NOT NULL CONSTRAINT [DF__sfATTRIBU__Manda__1B9317B3] DEFAULT ((0)),
[CustomFormat] [nvarchar](100) NULL,
CONSTRAINT [sfATTRIBUTES_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[sfATTRIBUTES] WITH CHECK ADD CONSTRAINT [sfATTRIBUTE_CATEGORIES.ID to CategoryID] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[sfATTRIBUTE_CATEGORIES] ([ID])
GO
ALTER TABLE [dbo].[sfATTRIBUTES] CHECK CONSTRAINT [sfATTRIBUTE_CATEGORIES.ID to CategoryID]
GO
ALTER TABLE [dbo].[sfATTRIBUTES] WITH CHECK ADD CONSTRAINT [sfENTITIES_sfATTRIBUTES_FK1] FOREIGN KEY([EntityType])
REFERENCES [dbo].[sfENTITIES] ([Typedef])
GO
ALTER TABLE [dbo].[sfATTRIBUTES] CHECK CONSTRAINT [sfENTITIES_sfATTRIBUTES_FK1]
GO
ALTER TABLE [dbo].[sfATTRIBUTES] WITH CHECK ADD CONSTRAINT [sfINTERNAL_CLASSES.ID to InternalClassID] FOREIGN KEY([InternalClassID])
REFERENCES [dbo].[sfINTERNAL_CLASSES] ([ID])
GO
ALTER TABLE [dbo].[sfATTRIBUTES] CHECK CONSTRAINT [sfINTERNAL_CLASSES.ID to InternalClassID]

USE [PPSAccountData]
GO
/****** Object: Table [dbo].[sfATTRIBUTE_VALUES] Script Date: 11/21/2008 03:57:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sfATTRIBUTE_VALUES](
[EntityID] [int] NOT NULL,
[AttributeID] [int] NOT NULL,
[SolutionID] [int] NULL,
[NumericValue] [float] NULL,
[CharValue] [nvarchar](4000) NULL,
[DateTimeValue] [datetime] NULL,
[DefinitionID] [int] NULL,
[Structure1EntryID] [int] NULL,
[Structure2EntryID] [int] NULL,
CONSTRAINT [IX_sfATTRIBUTE_VALUES_5] UNIQUE NONCLUSTERED
(
[EntityID] ASC,
[AttributeID] ASC,
[SolutionID] ASC,
[DefinitionID] ASC,
[Structure1EntryID] ASC,
[Structure2EntryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[sfATTRIBUTE_VALUES] WITH CHECK ADD CONSTRAINT [FK_sfATTRIBUTE_VALUES_sfATTRIBUTE_DEFINITIONS] FOREIGN KEY([DefinitionID])
REFERENCES [dbo].[sfATTRIBUTE_DEFINITIONS] ([ID])
GO
ALTER TABLE [dbo].[sfATTRIBUTE_VALUES] CHECK CONSTRAINT [FK_sfATTRIBUTE_VALUES_sfATTRIBUTE_DEFINITIONS]
GO
ALTER TABLE [dbo].[sfATTRIBUTE_VALUES] WITH CHECK ADD CONSTRAINT [FK_sfATTRIBUTE_VALUES_sfATTRIBUTES] FOREIGN KEY([AttributeID])
REFERENCES [dbo].[sfATTRIBUTES] ([ID])
GO
ALTER TABLE [dbo].[sfATTRIBUTE_VALUES] CHECK CONSTRAINT [FK_sfATTRIBUTE_VALUES_sfATTRIBUTES]
GO
ALTER TABLE [dbo].[sfATTRIBUTE_VALUES] WITH NOCHECK ADD CONSTRAINT [FK_sfATTRIBUTE_VALUES_sfSOLUTIONS] FOREIGN KEY([SolutionID])
REFERENCES [dbo].[sfSOLUTIONS] ([ID])
GO
ALTER TABLE [dbo].[sfATTRIBUTE_VALUES] CHECK CONSTRAINT [FK_sfATTRIBUTE_VALUES_sfSOLUTIONS]
GO
ALTER TABLE [dbo].[sfATTRIBUTE_VALUES] WITH CHECK ADD CONSTRAINT [sfSTRUCTURE_ENTRIES_sfATTRIBUTE_VALUES_FK1] FOREIGN KEY([Structure1EntryID])
REFERENCES [dbo].[sfSTRUCTURE_ENTRIES] ([ID])
GO
ALTER TABLE [dbo].[sfATTRIBUTE_VALUES] CHECK CONSTRAINT [sfSTRUCTURE_ENTRIES_sfATTRIBUTE_VALUES_FK1]
GO
ALTER TABLE [dbo].[sfATTRIBUTE_VALUES] WITH CHECK ADD CONSTRAINT [sfSTRUCTURE_ENTRIES_sfATTRIBUTE_VALUES_FK2] FOREIGN KEY([Structure2EntryID])
REFERENCES [dbo].[sfSTRUCTURE_ENTRIES] ([ID])
GO
ALTER TABLE [dbo].[sfATTRIBUTE_VALUES] CHECK CONSTRAINT [sfSTRUCTURE_ENTRIES_sfATTRIBUTE_VALUES_FK2]

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-20 : 23:34:55

Please post the query which contained left outer join so that we don't assume things.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-20 : 23:43:50

I started reading this, but I only got through chapter 7:). Can you please try to post sample data and desired output? Also try to sum up your questions in a few short paragraphs? It will be easier to assist.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 02:08:29

check if this is the problem

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 02:15:19

[code]SELECT a.ID,
a.AccountID,
a.InternalClassID,
a.CategoryID,
a.EntityType,
a.Name,
a.InternalName,
a.DataType,
a.ApplicationUsage,
a.IsCalculated,
a.Formula,
a.MinNumericValue,
a.MinDateValue,
a.MaxNumericValue,
a.MaxDateValue,
a.MaxStringLength,
a.Weight,
a.Mandatory,
a.CustomFormat,
v.EntityID,
v.AttributeID,
v.SolutionID,
v.NumericValue,
v.CharValue,
v.DateTimeValue,
v.DefinitionID,
v.Structure1EntryID,
v.Structure2EntryID
FROM sfATTRIBUTES AS a
LEFT JOIN sfATTRIBUTE_VALUES AS v ON v.AttributeID = a.ID
AND v.Structure1EntryID = 26150
WHERE a.EntityType = N'Project'[/code]

E 12°55'05.63"
N 56°04'39.26"

dan.ling@pcubed.com
Starting Member

4 Posts

Posted - 2008-11-21 : 08:54:56

I posted the wrong query by mistake, here is the correct query SQL:

SELECT sfATTRIBUTES.ID, sfATTRIBUTES.AccountID, sfATTRIBUTES.InternalClassID, sfATTRIBUTES.CategoryID, sfATTRIBUTES.EntityType,
sfATTRIBUTES.Name, sfATTRIBUTES.InternalName, sfATTRIBUTES.DataType, sfATTRIBUTES.ApplicationUsage, sfATTRIBUTES.IsCalculated,
sfATTRIBUTES.Formula, sfATTRIBUTES.MinNumericValue, sfATTRIBUTES.MinDateValue, sfATTRIBUTES.MaxNumericValue,
sfATTRIBUTES.MaxDateValue, sfATTRIBUTES.MaxStringLength, sfATTRIBUTES.Weight, sfATTRIBUTES.Mandatory, sfATTRIBUTES.CustomFormat,
sfATTRIBUTE_VALUES.EntityID, sfATTRIBUTE_VALUES.AttributeID, sfATTRIBUTE_VALUES.SolutionID, sfATTRIBUTE_VALUES.NumericValue,
sfATTRIBUTE_VALUES.CharValue, sfATTRIBUTE_VALUES.DateTimeValue, sfATTRIBUTE_VALUES.DefinitionID,
sfATTRIBUTE_VALUES.Structure1EntryID, sfATTRIBUTE_VALUES.Structure2EntryID
FROM sfATTRIBUTES LEFT OUTER JOIN
sfATTRIBUTE_VALUES ON sfATTRIBUTES.ID = sfATTRIBUTE_VALUES.AttributeID
WHERE (sfATTRIBUTES.EntityType = N'Project') AND (sfATTRIBUTE_VALUES.Structure1EntryID = 26150)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 09:24:10

E 12°55'05.63"
N 56°04'39.26"

dan.ling@pcubed.com
Starting Member

4 Posts

Posted - 2008-11-21 : 10:17:01

Thanks everyone for your input, the modifications provided by Peso worked. Using Peso’s modifications, here is a description of the expected results. Table “a” has a Primary Key named “ID” and contains 138 records with an entity type equal to “Project”. Table “v” has 433 records with a “Structure1ID” equal to 26150. Table “v” has a Foreign Key named “EntityID” that establishes the relationship between table “a” and “v”. I was expecting the query to return 138 records that would include all matching records and Null values for any missing table “a” records in table “v”. The query now returns all 138 records including Null values.

My original post was long but I though more info was better than less for the less experienced readers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources