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.
| Author |
Topic |
|
purisqlserver
Yak Posting Veteran
73 Posts |
 Posted - 2001-12-27 : 07:07:07
|
| sir,There are four tables Item_master,order,special,favourite.Item_master being the parent table with item_number as the primary key.All the other tables are child tables(order,special,favourite).If the input to the query is item_number,the list of tables should be given out,wherein the item_number exists.All the tables are related using a database diagram.In return,the query results have to be displayed using ASP(active server pages).Thanx |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-27 : 07:39:17
|
| Firstly, if these three tables(order,Special,favourite) contain only the item_no and some related information. then i would say its a wrong way of doing it. you can very well have one table which links with your Item_master and contains a status field wherein you can have multiple records with the same item_no but different Status(order,Special or Favourite) . this will simplify your design. Another thing if you want to add a new status you dont have to create another table.Anywayz for your existing table structure , something like this should help.CREATE PROCEDURE Diag_disp(@mitemno int) asdeclare @mTables varchar(100)if Exists(select * from item_master where item_no=@mitemno) begin if Exists(select * from order where item_no=@mitemno) select @mTables="Order " if Exists(select * from Special where item_no=@mitemno) select @mTabeles=@mTables+" Special" if Exists(select * from Favourite where item_no=@mitemno) select @mTabeles=@mTables+" Favourite" select @mTablesendHTH-------------------------Graz's Baby is my Master:)Edited by - Nazim on 12/27/2001 09:37:48 |
 |
|
|
purisqlserver
Yak Posting Veteran
73 Posts |
Posted - 2001-12-28 : 06:10:33
|
| sir,It worked fine."select @mTables"------The final select statement was displaying null,if the following statement was not executed."if Exists(select * from Special where item_no=@mitemno) select @mTabeles=@mTables+" Special""so,I initialized the variable @mTabeles="", it worked fine.Is there some other way of doing it?????Can query be written on the system tables to know the information of the related tables, if .....not used in ASP?????Thnx |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-28 : 14:32:57
|
quote:
Can query be written on the system tables to know the information of the related tables, if .....not used in ASP?????
I'm not sure if I'm understanding your question right, but if you build a stored procedure like the syntax that Nazim provided, then this stored procedure lives in SQL Server and can be called by ANY calling program... ASP, VB, C++, .Net, SQL Query Analyzer, etc., etc. So there's no direct tie to ASP this way.--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
purisqlserver
Yak Posting Veteran
73 Posts |
Posted - 2001-12-28 : 23:17:53
|
| sir,Can query be written on the system tables to know the information of the related tables????? |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-28 : 23:46:20
|
| Yeah, i forgot to initialize the variable, Sorry for that.If you want to know the information about the table. you dont have to use systables necessarily. Sql Server provies a stored procedure sp_help, that will give you whole information about any object. including the keys defined .or this query should help u.SELECT COL_NAME(OBJECT_ID(@TableName),ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @tablenamei have got this query from this site.search thru the site, u can get good information.HTH-------------------------Graz's Baby is my Master:)Edited by - Nazim on 12/29/2001 01:35:13 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-30 : 02:49:21
|
| I'd also recommend you research the sp_columns and sp_tables stored procedures as well as all of the INFORMATION_SCHEMA views.--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
|
|
|
|
|
database diagram query