Left Outer Join Help

Question

Left Outer Join Help

t-rev

SSC Rookie

Points: 43
More actions
January 16, 2007 at 9:58 am

#115689

See query below. I need to be able to pull the data in by fiscal_wk regardless of if there was any data. This example pulls in 2 product lines (Blue and Metal). Blue has sales every week for both years. Metal started selling in June 2006 so no data for 2005 or early 2006. I've been playing with this for a while and am not getting the correct results back. If I run only for Metal I only get weeks 21-52 instead of 1-52, where weeks 1-20 should display as
Metal
select
reporting_year,
sec_prod_group,
b.fiscal_wk,
sum(qty_sold)
from
depot_buckets b
left outer join
(select
  sec_prod_group,
  p.date_start,
  qty_sold
from
  depot_pos_item_sum p
  inner join depot_items i on i.idx_item = p.idx_item
where sec_prod_group in ('Metal','Blue')
&nbsp pos

on b.date_start = pos.date_start
where reporting_year in (2005,2006)
group by
reporting_year,
sec_prod_group,
b.fiscal_wk

Viewing 4 posts - 1 through 3 (of 3 total)

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

Can you post the DDL (data definition language) of the tables used, ie the CREATE statements? Only include the columns necessary if there's any issues with exposure. Also some example data from each table would be useful.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

t-rev SSC Rookie Points: 43 More actions · Answer 2

Is this what you're looking for:

CREATE TABLE [Depot_Items] (

[IDX_Item] [int] NOT NULL ,

[HD_SKU] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Prim_Prod_Group] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Sec_Prod_Group] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Tert_Prod_Group] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

&nbsp ON [PRIMARY]

GO

=====================

CREATE TABLE [Depot_POS_Item_Sum] (

[Date_Start] [datetime] NOT NULL ,

[IDX_Item] [int] NOT NULL ,

[Qty_Sold] [int] NULL ,

CONSTRAINT [PK_wrk_Depot_POS_Item_Sum] PRIMARY KEY NONCLUSTERED

(

[Date_Start],

[IDX_Item]

&nbsp WITH FILLFACTOR = 90 ON [PRIMARY] ,

CONSTRAINT [IX_wrk_Depot_POS_Item_Sum] UNIQUE CLUSTERED

(

[Date_Start],

[IDX_Item]

&nbsp WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

GO

===========================

CREATE TABLE [Depot_Buckets] (

[Date_Start] [datetime] NOT NULL ,

[Reporting_Year] [int] NULL ,

[Fiscal_Wk] [int] NULL ,

CONSTRAINT [PK_Depot_Buckets] PRIMARY KEY NONCLUSTERED

(

[Date_Start]

&nbsp WITH FILLFACTOR = 90 ON [PRIMARY] ,

CONSTRAINT [IX_Depot_Buckets] UNIQUE CLUSTERED

(

[Date_Start]

&nbsp WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

GO

========================

Examples

depot_buckets

date_start month report_year firstdayofmonth fiscal_yr fiscal_wk

2006-10-02 00:00:00.000 10 2006 2006-10-01 00:00:00.000 2006 36

depot_pos_item_sum

date_start idx_item unit dollars onhand

2006-10-02 00:00:00.000 3874 134 2 4.0000

depot_items

idx_item sku primary secondary tertiary

3874 101491 Metal Bronze fc120

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 3

Okay, I think I've got it... Try this as your derived table:

(select

sec_prod_group,

p.date_start,

qty_sold

from

depot_items i

LEFT join depot_pos_item_sum p

on i.idx_item = p.idx_item

where sec_prod_group in ( 'Metal', 'Blue' )

) pos

There will be no Metal rows returned if there are no rows in the dpot_pos_item_sum table.

Let me know how this goes, I'll keep my eye on this post, but sadly I'm going home now... Good luck

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]