Solved

Simple left join not working as expected

Posted on 2004-04-21
2
543 Views
Last Modified: 2008-02-20
Hello All.  I have a fairly simple LEFT JOIN in which I'm trying to get a list of counts and amounts grouped by a dimension.  I want a list of every row of my dimension, with the corresponding count and amount (from two separate tables), listing 0 if there isn't anything.  The following does not work properly:

SELECT
      a.seizure_disp_desc, count(Sub_Asset_Disp_Cd) AS Number, sum(Disp_amt) AS Amount
      FROM seizure_disp_cd_dim a
      LEFT JOIN t_sub_asset b ON seizure_disp_cd = sub_asset_disp_cd
      LEFT JOIN t_disposition c ON b.sub_asset_id = c.sub_asset_id
      WHERE Disp_Dt BETWEEN '1/1/01' AND '12/31/03'
      GROUP BY a.seizure_disp_desc

However, if I remove the WHERE clause, it does work, giving my 0's where I expect it.  

Can anyone explain to me what I am missing here and what I can do to make this work?  Thanks!

J
0
Comment
Question by:keithjd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10882198
Assuming that Disp_Dt is datetime, try changing:

     WHERE Disp_Dt BETWEEN '1/1/01' AND '12/31/03'
To
     WHERE Disp_Dt BETWEEN '2001-01-01' AND '2003-12-31'
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 50 total points
ID: 10882228
which table contains the Disp_DT

if it isn't seizure_disp_cd_dim
then the where clause will be excluding all non joined rows

so if disp_dt belongs on t_disposition
re-write as
SELECT
     a.seizure_disp_desc, coalesce(count(Sub_Asset_Disp_Cd),0) AS Number, coalesce(sum(Disp_amt),0.00) AS Amount
     FROM seizure_disp_cd_dim a
     LEFT JOIN t_sub_asset b
        ON seizure_disp_cd = sub_asset_disp_cd
     LEFT JOIN ( select *
                        from t_disposition
                       WHERE Disp_Dt BETWEEN '1/1/01' AND '12/31/03') as c
      ON b.sub_asset_id = c.sub_asset_id
     GROUP BY a.seizure_disp_desc

     hth
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question