Look up tables to generate a report!

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

Transact-SQL (2008)

Look up tables to generate a report!

Author

Topic

ravikiran
Starting Member

1 Post

Posted - 2012-11-30 : 06:32:06

Hi Gurus,

I'am quite new to SQL but keep using it :)

I need some help with generating a report from few tables as in the following example. It would be helpful if anyone can explain me how to do this job:

Table 1:
Quote Color Grade
1111 001 111
1112 002 222
1113 003 333
1114 004 444
1115 005 555

Table 2:
Id Color
001 Green
002 Violet
003 Red
004 Black
005 Orange

Table 3:
ID Grade
111 Grade A
222 Grade B
333 Grade C
444 Grade D
555 Grade E

Report:
Quote Color Grade
1111 Green Grade A
1112 Violet Grade B
1113 Red Grade C
1114 Black Grade D
1115 Orange Grade E

I'll be using this on a mass scale for about 20 to 30 tables :) in a VBA module.

Thanks in advance,

cheers,
Ravi.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-30 : 06:57:38

Create a stored procedure to deliver the data to the report.
create proc s_rpt
as
select t1.Quote, t2.Color, t3.Grade
from Table1 t1
join Table2 t2 on t2.ID = t1.Color
join Table3 t3 on t3.ID = t1.Grade
order by t1.Quote

(You know you've mispelt Colour? :) )

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources