USING SUBQUERY IN SELECT 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

SQL Server 2000 Forums

Transact-SQL (2000)

USING SUBQUERY IN SELECT STATEMENT

Author

Topic

loiseaud
Starting Member

1 Post

Posted - 2006-08-28 : 16:11:51

Hello,

I hope you will be able to help me :

I am looking at reusing the results of a first query in a second select statement.

I have a first table "ACTIVE_FIELDS" in which I maintain the columns I want to query.

Table name ACTIVE_FIELDS

-----------------------------------------
Field - Status
----------------------------------------
CODE - ACTIVE
DESCRIPTION - ACTIVE
NAME - INACTIVE

So I get the list of active fields by doing :

SELECT FIELD FROM ACTIVE_FIELDS WHERE STATUS=ACTIVE

It gives me : Code and Description.

I have then a second table MATERIAL with 3 columns CODE, DESCRIPTION and NAME.

I am looking at doing a query against MATERIAL table as follows

SELECT (SELECT FIELD FROM ACTIVE_FIELDS WHERE STATUS=ACTIVE) from MATERIAL and I expect to only get columns CODE and DESCRIPTION in the result.

If I want to add the NAME field in the result of my query, I just have to change status of NAME field in the eh ACTIVE_FIELDS table..

Can anybody help me with the exact syntax, I must miss a function to convert the result of the first query..

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-28 : 16:26:58

loiseaud,

Please post your actual table schema, some sample data from all tables, and the expected output. I'm sure with enough information someone here can help you.

Ken

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-08-28 : 18:06:55

Try this

DECLARE @Sql nvarchar(1000)
DECLARE @Fields nvarchar(100)
SET @Fields = ''
SELECT @Fields = @Fields + rtrim(Field) + ',' FROM ACTIVE_FIELDS WHERE Status = 'ACTIVE'
SET @Sql = 'SELECT ' + substring(@Fields, 1, len(@Fields) - 1)
+ ' FROM MATERIAL'
exec sp_executesql @Sql

rajam
Starting Member

1 Post

Posted - 2006-09-01 : 02:53:35

alter procedure prc_active_fields as
begin
declare cur_active_fields cursor for select field from active_fields where status = 'active'
declare @sql_string varchar(200)
declare @fields varchar(200)
declare @field varchar(200)
declare @status int
open cur_active_fields
set @status=0
fetch cur_active_fields into @field

while (@@fetch_status = 0)
begin
if (@status = 0)
begin
set @fields = @field
end
else
begin
set @fields = @fields + ',' + @field
end
fetch cur_active_fields into @field
set @status = 1

end
if (@status = 1)
begin
set @sql_string = 'select '+@fields+' from material'
exec (@sql_string)
print @sql_string
end
close cur_active_fields
deallocate cur_active_fields
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources