Not sure how to make this statement

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

Development Tools

ASP.NET

Not sure how to make this statement

Author

Topic

demausdauth
Starting Member

17 Posts

Posted - 2008-04-18 : 15:26:29

Using VS2005 hitting SQL 2005 from a VB.Net windows application

I have 2 tables Policy, Producer

Policy contains 4 fields Prod1, Prod2, Prod3, Prod4 (among others)
Producer contains ProdNumber, Name

What I want to do is Select from the Policy table and for each of the Prod1-4 fields I would like to get the Name value from the Producer table and put it in a temp column called PolicyProd1-4

So at the end:
Policy return 8 columns

Is this even possible?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-18 : 15:41:23

Your problem is just a badly designed database

select p.Prod1, PolicyProd1 = pr1.Name,
p.Prod2, PolicyProd2 = pr2.Name,
p.Prod3, PolicyProd3 = pr3.Name,
p.Prod4, PolicyProd4 = pr4.Name,
from Policy p
join Producer pr1
on p.Prod1 = pr1.ProdNumber
join Producer pr2
on p.Prod2 = pr2.ProdNumber
join Producer pr3
on p.Prod3 = pr3.ProdNumber
join Producer pr4
on p.Prod4 = pr4.ProdNumber

You would probably want to make all those left joins as I suspect you might not have all 4 products for a policy.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

demausdauth
Starting Member

17 Posts

Posted - 2008-04-18 : 15:59:03

Works beautifully, my example is only a small part of a larger join statement. When I integrated what you posted it works great.

Thanks a lot nr !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources