Is there an easier way to do this outer join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Is there an easier way to do this outer join?

Author

Topic

vr8ce
Starting Member

23 Posts

Posted - 2005-04-04 : 19:50:09

I'm trying to outer join two tables, but there are two values from the right side that I have to have, and I can't figure out an easy to do this.

DECLARE @aging table (dt smalldatetime, cd int, bkt tinyint, cnt int, amt money)
INSERT INTO @aging VALUES ('4/4/2005', 1, 1, 1, 10)
INSERT INTO @aging VALUES ('4/4/2005', 1, 3, 3, 30)
INSERT INTO @aging VALUES ('4/4/2005', 2, 2, 2, 20)
INSERT INTO @aging VALUES ('4/5/2005', 2, 3, 3, 30)

DECLARE @bkt table (bkt tinyint)
INSERT INTO @bkt VALUES (1)
INSERT INTO @bkt VALUES (2)
INSERT INTO @bkt VALUES (3)
INSERT INTO @bkt VALUES (4)

I want every bucket for every code for a given date. So, given the above data, I want this result set for 4/4/2005:
4/4/2005,1,1,1,10
4/4/2005,1,2,0,0
4/4/2005,1,3,3,30
4/4/2005,1,4,0,0
4/4/2005,2,1,0,0
4/4/2005,2,2,2,20
4/4/2005,2,3,0,0
4/4/2005,2,4,0,0

My ugly solution involves creating another temp table.

DECLARE @cd_bkt table (dt smalldatetime, cd int, bkt tinyint)
INSERT INTO @cd_bkt
SELECT c.dt, c.cd, b.bkt
FROM (SELECT DISTINCT dt, cd FROM @aging WHERE dt = '4/4/2005') c
CROSS JOIN @bkt b

SELECT a.dt, a.cd, a.bkt, ISNULL(SUM(d.cnt),0), ISNULL(SUM(d.amt),0)
FROM @aging d RIGHT JOIN @cd_bkt a ON d.dt = a.dt and d.cd = a.cd AND d.bkt = a.bkt
WHERE a.dt = '4/4/2005'
GROUP BY a.dt, a.cd, a.bkt
ORDER BY 2, 3

I can't just outer join on bucket, because if the bucket doesn't exist for a given code for the queried date, I get a NULL in the code. But the above seems terribly inefficient (the aging table has 100K rows in it, of which 5-10K are for a given date, and the @cd_bkt table will have 12K+ rows in for a given date). Is there an easier way I'm missing?

Thanks!

Vince

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-04 : 20:02:37

This is probably better assuming you are properly indexed on your real tables.



SELECT a.dt, a.cd, a.bkt, ISNULL(SUM(d.cnt),0), ISNULL(SUM(d.amt),0)
FROM
(
	SELECT c.dt, c.cd, b.bkt
	FROM (SELECT DISTINCT dt, cd FROM @aging WHERE dt = '4/4/2005') c
	CROSS JOIN @bkt b
) a 
LEFT OUTER JOIN @aging d
ON d.dt = a.dt and d.cd = a.cd AND d.bkt = a.bkt
WHERE a.dt = '4/4/2005'
GROUP BY a.dt, a.cd, a.bkt
ORDER BY 2, 3

So instead of using a table variable or a temp table, we use a derived table. I switched your RIGHT OUTER JOIN to LEFT OUTER JOIN also as a query that has a LEFT OUTER JOIN is easier to understand than one with a RIGHT OUTER JOIN.

Tara

PW
Yak Posting Veteran

95 Posts

Posted - 2005-04-04 : 20:11:05

How about left-joining @aging to a derived table that contains all the required cd/bkt combinations. Don't think you need any aggregates or Group By's in this case ?

 
Select a.dt, dt.cd, dt.bkt, IsNull(a.cnt, 0) as cnt, IsNull(a.amt, 0) As amt
From
(
  Select Distinct a.cd, b.bkt
  From @aging As a
  Cross Join @bkt As b
  Where a.dt = '4/4/2005'
) dt
Left Join @aging As a
  On (a.cd = dt.cd And
      a.bkt = dt.bkt And
      a.dt = '4/4/2005')
Order By 2, 3

vr8ce
Starting Member

23 Posts

Posted - 2005-04-04 : 21:02:49

quote:
Originally posted by PW

How about left-joining @aging to a derived table that contains all the required cd/bkt combinations. Don't think you need any aggregates or Group By's in this case?

Thanks to both of you. I should probably have mentioned I understand I can replace the tmp table with a derived one. That's arguably cleaner (one less table), but I don't see that it's more efficient. It's still having to join two 10K row tables in memory, i.e. without an index.

As to the group by's, the more I look at the data, the less I think I needed them in my original posting, either. So yes, they can probably be eliminated (I'll have to look a little closer at the source data to make sure).

Thanks again,

Vince

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources