a left outer join question - SQL Server Forums

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

SQL Server Development (2000)

a left outer join question

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-15 : 19:01:11

Hello there Guys
I have two tables one is a alerts table
with the following schema:

prodcode
saledate
store
val
cost
alertReason

and I have another one called the reduced table
with the following schema:

prodcode
saledate
store
val
cost
reason (whos value is ALWAYS RTC (Reduced to clear))

Now this reduced table gets updated everyday and what I am trying to achieve is to get all records in the alerts table that dont have a joining saledate and prodcode and store to be moved to a seperate non reduced table for reporting purposes.

This procedure must happen everyday as both tables get inserted into daily (from external dat sources) for that specific day around the
same time.

What I came up with was something like this but can forsee it becoming a resource lag as it has to delete all contents and re import the same again:

DROP TABLE NoReduced

SELECT a.date, a.prodcode, a.store, a.val, a.cost, a.error
INTO NoReduced
FROM Alerts a LEFT OUTER JOIN
Reduced r ON a.prodcode = r.prodcode
AND a.saledate = r.saledate
AND a.store = r.store
WHERE r.val is null or
r.cost is null or
r.reason is null

Everyday ther is approx 30 thousand records going into each of them but only for the current date the historic data remains consistant.

Is there any better more effecient less redundant way to do this ?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 21:15:16

Can't you just query those tables - is there really a need for the extra table every day?

If you do need the extra table, you could try truncating it instead of dropping it, and then using an INSERT ... SELECT to get the data. Other than that, if the rows are different every day and you really need a separate copy of them, there won't be a much better way to do it.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-16 : 05:19:52

Thanks SN
Could you reccomend any indexing for this maybe to improve performance ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 05:30:37

No need for three WHEREs. Just check for one of the columns in the JOIN binding.

SELECT		a.date,
		a.prodcode,
		a.store,
		a.val,
		a.cost,
		a.error
INTO		NoReduced 
FROM		Alerts a
LEFT JOIN	Reduced r ON a.prodcode = r.prodcode AND a.saledate = r.saledate AND a.store = r.store
WHERE		r.prodcode is null

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 11:15:53

quote:
Originally posted by Jim77

Thanks SN
Could you reccomend any indexing for this maybe to improve performance ?

Have you run it through the index tuning wizard? Without seeing your data it's hard for me to say, but the obvious columns to be considering in each table are the three that you use in the join.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-16 : 12:17:01

thanks guys I have never used the tuning wizard but I think that will change.
cheers again !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources