Trying to avoid multiple joins and/or a cursor

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)

Trying to avoid multiple joins and/or a cursor

Author

Topic

rachelreese
Starting Member

4 Posts

Posted - 2003-11-11 : 15:47:39

Hi folks,

I have a couple pretty simple tables. They represent whether or not someone has purchased our product on a special, and when (if) they renew their dues, whether or not they will have to pay the standard rate.

table: offer_renew
columns: offer_id, renew_id,

table: offer
columns: renew_id, offer_rate_type_cd

So, offer_id is the id of the current offer (plan, subscription rate), renew_id is the offer that they will renew to. Offer_rate_type_cd is either "special" or "standard". Someone can renew from one special offer to another special offer. What I'm looking for is the quickest (as in running time) way to find out the number of renewals until "standard", given the current offer_id. I can't create a new permanent table (the sane solution), and I'd prefer not to use a cursor. I can just join back onto these same tables until I find a renew_id with offer_rate_type_cd = standard, and count, but there has to be a quicker solution.

Here's a sample in case it doesn't make sense:

table: offer_renew
columns: offer_id, renew_id,
2 8
5 6
8 5

table: offer
columns: renew_id, offer_rate_type_cd
2 special
6 standard
5 special
8 special

so the number I want is 3. The customer renews from offer 2 to offer 8, then offer 8 renews to offer 5, then offer 5 renews to offer 6, which is standard offer type.

Does that make sense?

Any ideas?
-Rachel

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-11 : 16:29:13

[code]

create table #t (lev int, child int)
declare @lev int,
@child int

set @lev=0
set @child = 2 --This defines the starting offer_id

insert into #t select @lev, @child

while @@rowcount>0
begin
set @lev=@lev+1
insert into #t select @lev, renew_id from offer_renew
where offer_id in (select child from #t where lev=@lev-1)
end

select lev NumOfRenewals from #t t
join offer o on o.renew_id = t.child
where offer_rate_type_cd = 'standard'
order by lev

drop table #t
[/code]

ps. Thanks Stoad...

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-11 : 16:29:30

Yeah,

You set up a hierarchy...When you say 3, you mean the third row...which the key is 5..no problem there..

Is there a set number of relationships? Or can it be n?

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-11 : 16:48:17

LOL, Jay... Ever read this in BOL?? :)

Solutions to Common Query Puzzles
When you work with data, some situations are easy to solve with a query, either simple or complex. Other times, solutions are not so clear. Among some of the more complicated solutions are:

Cross-tab reports in which summary information is created. Most cross-tab reports can be generated by just using either the CASE function or the CUBE or ROLLUP options of SELECT.
Expanding hierarchies in which the hierarchical steps of getting to a particular result must be shown, for example, tracing a genealogical family tree from a great-great-grandparent to yourself.
Expanding networks in which the multiple hierarchical steps must be shown; for example, all flights originating from Seattle and landing in New York.
There are now many books available on SQL in general, and several books on Transact-SQL itself. These books have solutions to many more query problems.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-11 : 16:50:30

I would rather read SQL Team

rachelreese
Starting Member

4 Posts

Posted - 2003-11-11 : 16:53:53

nice!

Yeah, the # of relationships isn't set, it can go up to n.

There can be multiple renew_ids for each offer_id, too, but it's not fair to bring that up at this point. :) I can get that one on my own.

Thanks!!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-11 : 16:56:00

Oho, Jay... this is exactly my case... :)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-11 : 17:03:49

Yes, These are the living BOL !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources