Join Metadata to Underlying Table

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)

Join Metadata to Underlying Table

Author

Topic

slserra
Starting Member

19 Posts

Posted - 2003-11-22 : 03:49:58

I am developing a web front end to a sql server database. The app allows a user to create SQL tables and views and query those tables
using the web interface.

I need to create a query that joins the metadata table (tbl_DatabaseField) with the underlying sql table (tbl_68) and returns the metadata information along with the corresponding value in the underlying table. The name of the underlying SQL table is derived as 'tbl_'+DatabaseId.

The following data shows the 3 tables in question and the desired output. There is only one tbl_DatabaseField table but there can be N tbl_xx tables where xx is a unique Database identifier value.

Can anyone please help me with this one. I've been staring at it for hours and keep going in circles.

Thanks,

Steve

CREATE TABLE [dbo].[tbl_DatabaseField] (
[PKId] [int] NOT NULL ,
[DatabaseId] [int] NULL ,
[SortOrder] [int] NULL ,
[FieldTitle] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_68] (
[PKId] [int] NOT NULL ,
[Column_73] [varchar] (100) NULL ,
[Column_74] [varchar] (30) NULL ,
[Column_75] [varchar] (30) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_70] (
[PKId] [int] NOT NULL ,
[Column_90] [varchar] (30) NULL ,
[Column_91] [varchar] (30) NULL ,
[Column_92] [varchar] (30) NULL ,
[Column_93] [varchar] (30) NULL
) ON [PRIMARY]
GO

INSERT tbl_DatabaseField VALUES (73,68,0,'Name')
INSERT tbl_DatabaseField VALUES (74,68,1,'Addr')
INSERT tbl_DatabaseField VALUES (75,68,2,'Phone')
INSERT tbl_DatabaseField VALUES (90,70,2,'Grade')
INSERT tbl_DatabaseField VALUES (91,70,1,'Teacher')
INSERT tbl_DatabaseField VALUES (92,70,4,'Subject')
INSERT tbl_DatabaseField VALUES (93,70,3,'Time')

INSERT tbl_68 VALUES (1,'Steve','1 Main St','555-1221')
INSERT tbl_68 VALUES (2,'Bill','44 March Lane','944-8989')
INSERT tbl_70 VALUES (1,'A','Mrs. Smith','Math','8:00')
INSERT tbl_70 VALUES (2,'A','Mrs. Wilson','History','10:00')
INSERT tbl_70 VALUES (3,'B','Mr. Jones','English','11:00')

Desired Output - Pass in a @DatabaseId and a @RowId

If @DatabaseId=68 and @RowId = 1:
PKId DatabaseId SortOrder FieldTitle Val
---- ---------- --------- ---------- -----
73 68 0 Name Steve
74 68 1 Addr 1 Main St
75 68 2 Phone 555-1221

if @DatabaseId=68 and @RowId = 2:
PKId DatabaseId SortOrder FieldTitle Val
---- ---------- --------- ---------- -----
73 68 0 Name Bill
74 68 1 Addr 44 March Lane
75 68 2 Phone 944-8989

if @DatabaseId=70 and @RowId = 2:
PKId DatabaseId SortOrder FieldTitle Val
---- ---------- --------- ---------- -----
91 70 1 Teacher Mrs. Wilson
90 70 2 Grade A
93 70 3 Time 10:00
92 70 4 Subject History

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-22 : 06:30:19

Hi

First up, THANK YOU for providing DDL, it makes our job much easier

There is no way for SQL Server to dynamically choosing what table to select from. You need to use dynamic SQL for this : http://www.sqlteam.com/SearchResults.asp?SearchTerms=dynamic+sql

Damian

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-22 : 07:47:43

Woah, DDL with desired inputs and outputs for a member with 10 posts, NICE JOB!!

Yeah, you'll need dynamic sql for this. One does need to question allowing for users to create tables. Maybe you can explain your requirements and we can suggest a solution that gets away from the creation of tables and the dynamic sql.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

slserra
Starting Member

19 Posts

Posted - 2003-11-22 : 08:01:07

Yeah, I figured I needed to use Dynamic SQL. I've spent a lot of time reading up:
http://www.sqlteam.com/item.asp?ItemID=2368 - Could not get this one to work with a table that had a primary key defined. It only returned a string containg data from the last row. Take the order by clause off and it works. Keep the order by clause and remove the PK and it works. I think if I could get past this issue I could get it to work since I can build a list of column names from the metadata table using synamic sql. I guess the last resort is to use the original cursor-based approach used in the above link.

The alternative I investigated was using the INFORMATION_SCHEMA.column tables to try to get it to match up. Not sure if this is a good approach or not. It got to be late and I threw in the towel and asked for help.

Anyway, the requirement is for users to be able to create ad-hoc database tables and views without having direct access to sql server. This is for an interactive intranet application where members can create their own content. The 'database' functionality within the intranet allows them to create tables, views, manage access permissions and import/export.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-22 : 18:02:00

It works fine:

CREATE TABLE [dbo].[tbl_DatabaseField] (
[PKId] [int] NOT NULL ,
[DatabaseId] [int] NULL ,
[SortOrder] [int] NULL ,
[FieldTitle] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_68] (
[PKId] [int] NOT NULL ,
[Column_73] [varchar] (100) NULL ,
[Column_74] [varchar] (30) NULL ,
[Column_75] [varchar] (30) NULL
) ON [PRIMARY]
GO
INSERT tbl_DatabaseField VALUES (73,68,0,'Name')
INSERT tbl_DatabaseField VALUES (74,68,1,'Addr')
INSERT tbl_DatabaseField VALUES (75,68,2,'Phone')
INSERT tbl_DatabaseField VALUES (90,70,2,'Grade')
INSERT tbl_DatabaseField VALUES (91,70,1,'Teacher')
INSERT tbl_DatabaseField VALUES (92,70,4,'Subject')
INSERT tbl_DatabaseField VALUES (93,70,3,'Time')
INSERT tbl_68 VALUES (1,'Steve','1 Main St','555-1221')
INSERT tbl_68 VALUES (2,'Bill','44 March Lane','944-8989')

declare @DatabaseId int, @RowId int
declare @col int, @val varchar(20), @sql nvarchar(800)
set @DatabaseId=68 set @RowId=1

create table #t (PKId int, DatabaseId int, SortOrder int,
FieldTitle varchar(20), Val varchar(20))
insert into #t select *, null from tbl_DatabaseField where DatabaseId=@DatabaseId

set @col=-1
while 0=0
begin
select @col=min(PKId) from #t where PKId>@col
if @col is null break
set @sql='select @val=column_'+cast(@col as varchar)+
' from tbl_'+cast(@DatabaseID as varchar)+' where PKId='+cast(@RowID as varchar)
exec sp_executesql @sql, N'@val varchar(20) output', @val output
update #t set val=@val where PKId=@col
end

select * from #t

drop table #t
drop table [dbo].[tbl_DatabaseField]
drop table [dbo].[tbl_68]

slserra
Starting Member

19 Posts

Posted - 2003-11-22 : 20:20:38

Works great! Thanks!

Now I need to study it a bit to understand how it works. There's always something more to learn...

Steve

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-22 : 20:44:00

Glad to hear, Steve, you liked it..

No doubt you'll understand it OK and add needful tweaks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources