OUTER JOIN not working?

[kamin]

I have this query

select
created_date,
tyg_aging_due_dates.object_type,
tyg_aging_due_dates.due_date
from
#tyg_aging_service_metrics
right join
tyg_aging_due_dates
on tyg_aging_due_dates.due_date =
#tyg_aging_service_metrics.due_date
and tyg_aging_due_dates.object_type =
#tyg_aging_service_metrics.object_type
order by PS, tyg_aging_due_dates.due_date

Basically the table tyg_aging_due_dates have this for data
object_type due_date
----------- --------
report 1/1/2005
report 1/1/2006
report 1/1/2007
image 2/1/2006
image 2/4/2006

The temporary table retuns something similar
created_date object_type due_date
------------ ----------- --------
6/1/2006 report 1/1/2005
6/10/2006 image 2/4/2006
So basically I want to join the two tables and for the due date's that
are missing from my temporary table, I want to display NULL (thus the
right join).

So my query would return
created_date object_type due_date
------------ ----------- --------
6/1/2006 report 1/1/2005
NULL report 1/1/2006
NULL report 1/1/2007
NULL image 2/1/2006
6/10/2006 image 2/4/2006

The date fields are of smalldatetime. Object type is a varchar(15).

When I run my query though, I get only 2 rows back. No matter if I
switch the right join to a left, full, inner, whatever. I still only
get 2 rows back. Is this a known issue?

I am running Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May
3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

[rhaazy]

Please post propper DDL and table creation scripts and some sample data
so that we may have a better understanding of the problem.

[kamin]

rhaazy wrote:

Please post propper DDL and table creation scripts and some sample data
so that we may have a better understanding of the problem.

Scripts are as follows:

CREATE TABLE [dbo].[tyg_aging_due_dates] (
[due_date] [smalldatetime] NOT NULL ,
[object_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL
) ON [PRIMARY]

-- Data
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-07-10', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-08-14', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-09-11', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-07-24', 'yg_decisionnote')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-08-28', 'yg_decisionnote')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-09-25', 'yg_decisionnote')

CREATE TABLE #tyg_aging_service_metrics (
[created_date] [datetime] NOT NULL DEFAULT (getdate()),
[due_date] [smalldatetime] NOT NULL ,
[res_ref_id] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL ,
[object_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL) ON [PRIMARY]

-- Data
insert into #tyg_aging_service_metrics (created_date, due_date,
res_ref_id, object_type)
values ('7/1/2006', '2006-08-14 00:00:00', '1234', 'yg_report')
insert into #tyg_aging_service_metrics (created_date, due_date,
res_ref_id, object_type)
values ('7/1/2006', '2006-09-25 00:00:00', '5524', 'yg_decisionnote')
So you see my temp table only has two records. I want to return all
the rows from the due_dates table regardless if they match those
records in tyg_aging_service_metrics.

[kamin]

For the output, for each row in tyg_aging_service_matrix, I want to
return all the records for tyg_aging_due_dates

So in my example, there should be 10 rows returned. Their will be 8
created_date fields that are null when it's done. This is what i'm
trying to do.
ka***@yankeegroup.com wrote:

rhaazy wrote:

Please post propper DDL and table creation scripts and some sample data
so that we may have a better understanding of the problem.

Scripts are as follows:

CREATE TABLE [dbo].[tyg_aging_due_dates] (
[due_date] [smalldatetime] NOT NULL ,
[object_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL
) ON [PRIMARY]

-- Data
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-07-10', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-08-14', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-09-11', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-07-24', 'yg_decisionnote')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-08-28', 'yg_decisionnote')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-09-25', 'yg_decisionnote')

CREATE TABLE #tyg_aging_service_metrics (
[created_date] [datetime] NOT NULL DEFAULT (getdate()),
[due_date] [smalldatetime] NOT NULL ,
[res_ref_id] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL ,
[object_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL) ON [PRIMARY]

-- Data
insert into #tyg_aging_service_metrics (created_date, due_date,
res_ref_id, object_type)
values ('7/1/2006', '2006-08-14 00:00:00', '1234', 'yg_report')
insert into #tyg_aging_service_metrics (created_date, due_date,
res_ref_id, object_type)
values ('7/1/2006', '2006-09-25 00:00:00', '5524', 'yg_decisionnote')
So you see my temp table only has two records. I want to return all
the rows from the due_dates table regardless if they match those
records in tyg_aging_service_metrics.

[Hugo Kornelis]

On 7 Jul 2006 11:04:59 -0700, ka***@yankeegroup.com wrote:

>For the output, for each row in tyg_aging_service_matrix, I want to
return all the records for tyg_aging_due_dates

So in my example, there should be 10 rows returned. Their will be 8
created_date fields that are null when it's done. This is what i'm
trying to do.

Hi Kamin,

I don't know why you expect 10 rows, since the sample data for
tyg_aging_due_dates has jjust 6 rows. Maybe you meant to add four more?

Anmyway, I tried the query yoou had in your first post, and I got an
error because you attempt to order by a non-existing column named PS.
After removing the ORDER BY clause, I got six rows. Two with a valid
date in created_date; the remaining four had NULLS:

created_date object_type due_date
----------------------- --------------- -----------------------
NULL yg_report 2006-07-10 00:00:00
2006-07-01 00:00:00.000 yg_report 2006-08-14 00:00:00
NULL yg_report 2006-09-11 00:00:00
NULL yg_decisionnote 2006-07-24 00:00:00
NULL yg_decisionnote 2006-08-28 00:00:00
2006-07-01 00:00:00.000 yg_decisionnote 2006-09-25 00:00:00

--
Hugo Kornelis, SQL Server MVP