Help me in figuring out some if or some loop

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)

Help me in figuring out some if or some loop

Author

Topic

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-25 : 21:32:28

Hi Guys,
This is the code which i have right now to see five levels but i need to give some if statement saying that if level2 is empty then show only those records where there are values in level1 and same way if level 3 is empty then it should be those records with level 1 and 2 in this form:a/b in one record and if level 4 is empty then it should show up to three levels in this form a/b/c and so on.Can you tell me some loop or some if statement.Thanks in advance

select di_categorydescs.category_description,desc1_view.Pub_id,desc1_view.Section_id,desc1_view.Category_id,desc1_view.numberofTimes,desc1_view.Level1,desc1_view.Level2,desc1_view.Level3,desc1_view.Level4,desc1_view.Level5 from di_categorydescs,desc1_view
where desc1_view.pub_id=di_categorydescs.pub_id AND
desc1_view.Level1=di_categorydescs.category_lvl_id
UNION
select di_categorydescs.category_description,desc1_view.Pub_id,desc1_view.Section_id,desc1_view.Category_id,desc1_view.numberofTimes,desc1_view.Level1,desc1_view.Level2,desc1_view.Level3,desc1_view.Level4,desc1_view.Level5 from di_categorydescs,desc1_view
where desc1_view.pub_id=di_categorydescs.pub_id AND
desc1_view.Level2=di_categorydescs.category_lvl_id
UNION
select di_categorydescs.category_description,desc1_view.Pub_id,desc1_view.Section_id,desc1_view.Category_id,desc1_view.numberofTimes,desc1_view.Level1,desc1_view.Level2,desc1_view.Level3,desc1_view.Level4,desc1_view.Level5 from di_categorydescs,desc1_view
where desc1_view.pub_id=di_categorydescs.pub_id AND
desc1_view.Level3=di_categorydescs.category_lvl_id
UNION
select di_categorydescs.category_description,desc1_view.Pub_id,desc1_view.Section_id,desc1_view.Category_id,desc1_view.numberofTimes,desc1_view.Level1,desc1_view.Level2,desc1_view.Level3,desc1_view.Level4,desc1_view.Level5 from di_categorydescs,desc1_view
where desc1_view.pub_id=di_categorydescs.pub_id AND
desc1_view.Level4=di_categorydescs.category_lvl_id
union
select di_categorydescs.category_description,desc1_view.Pub_id,desc1_view.Section_id,desc1_view.Category_id,desc1_view.numberofTimes,desc1_view.Level1,desc1_view.Level2,desc1_view.Level3,desc1_view.Level4,desc1_view.Level5 from di_categorydescs,desc1_view
where desc1_view.pub_id=di_categorydescs.pub_id AND
desc1_view.Level5=di_categorydescs.category_lvl_id
Thanks in advance

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-25 : 21:43:39

HI Guys,
I am getting the same output with this also.So please let me know about if or while loop. Thanks

select di_categorydescs.category_description,desc1_view.Pub_id,desc1_view.Section_id,desc1_view.Category_id,desc1_view.numberofTimes,desc1_view.Level1,desc1_view.Level2,desc1_view.Level3,desc1_view.Level4,desc1_view.Level5 from di_categorydescs,desc1_view
where desc1_view.pub_id=di_categorydescs.pub_id AND
desc1_view.Level1=di_categorydescs.category_lvl_id OR
desc1_view.Level2=di_categorydescs.category_lvl_id OR
desc1_view.Level3=di_categorydescs.category_lvl_id OR
desc1_view.Level4=di_categorydescs.category_lvl_id OR
desc1_view.Level5=di_categorydescs.category_lvl_id

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-25 : 23:19:19

can you do a join instead of union? this way you eliminate what you don't need.

select <fields> from tbl1 t1
join tbl2 t2
on t1.id=t2.id
join tbl3 t3
on t2.id=t3.id
join ...

--------------------
keeping it simple...

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-25 : 23:25:22

Hi,
I removed union.Look at the code above yours.This is without union.Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources