see if a field existist in a subquery

I know this isn't right but I want to find out if EDITID is one of the fields in the query... how would I do this?  If it does return something EDIT should be 1 otherwise it should be zero

SELECT
last name
 EDITID = select EDITID from table2where Field = '234'
from table
vbnetcoderAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Could it become Scaler ?

Is there any correlation to the outer query ?

Would help if you could provide some dummy data - just a few rows would help.


e.g. making it scaler...
SELECT  lastname, case when 'somevalue' in (select EDITID from #table2 where Field = '234') then 'Y' else 'N' end as edited
from #table

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't understand your question.
Maybe if you provide the table schemas it might help us understand it better.
0
 
Mark WillsTopic AdvisorCommented:
Well, there are a few ways it could be achieved.....

The way you have your psuedo code is actually one of the ways - it is called an inline subquery so long as it only returns 1 result (ie it is scaler)

SELECT  lastname, (select EDITID from table2 where Field = '234') as edited
from table

Open in new window


Now, you can also apply any other T-SQL to that scaler result. For example, use in a case statement, check if NULL and so on. If need be, in the inner subquery, you could use aggregate functions like max(), count() or top 1 to make sure it is scaler.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
>>I know this isn't right but I want to find out if EDITID is one of the fields in the query... how would I do this?  If it does return something EDIT should be 1 otherwise it should be zero
Yes it is possible. We just have to use some system tables for checking the column existence.

I have demonstrated both the cases below with sample data , solution with the tested output below.

Sample solution for you

/*CASE 1 : EDITID Exists in yourtable2 */

Table Creation and data generation
--

CREATE TABLE Q1
(
	 [last name] varchar(10)
	,EditId INT
)
GO

INSERT INTO Q1 VALUES ( 'a' , 1 )
GO

CREATE TABLE Q2
(
	 Field varchar(10)
	,EditId INT
)
GO

INSERT INTO Q2 VALUES ( '234' , 15 )
GO

--

Open in new window


SOLUTION

--

IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'EDITID' AND Object_ID = Object_ID(N'dbo.Q2'))
BEGIN
	EXEC(' SELECT [last name], ( SELECT TOP 1 EDITID from Q2 where Field = ''234'' ) EDITID 
	from Q1	' ) 
END
ELSE
BEGIN
	SELECT [last name],	EDITID  = 0
	from Q1
END

--

Open in new window


OUTPUT

--

/*------------------------
OUTPUT
------------------------*/
last name  EDITID
---------- -----------
a          15

(1 row(s) affected)



--

Open in new window


/*CASE 2 : EDITID DOES NOT Exists in yourtable2 */

Using the above script, Just drop the EditId column
--

ALTER TABLE Q2 
	DROP COLUMN EditId

--

Open in new window


SOLUTION

--

IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'EDITID' AND Object_ID = Object_ID(N'dbo.Q2'))
BEGIN
	EXEC(' SELECT [last name], ( SELECT TOP 1 EDITID from Q2 where Field = ''234'' ) EDITID 
	from Q1	' ) 
END
ELSE
BEGIN
	SELECT [last name],	EDITID  = 0
	from Q1
END

--

Open in new window


OUTPUT

--

/*------------------------
OUTPUT
------------------------*/
last name  EDITID
---------- -----------
a          0

(1 row(s) affected)



--

Open in new window


2 More Versions ---

VERSION 2

--

IF EXISTS(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = 'Q2' AND [COLUMN_NAME] = 'EDITID')
BEGIN
	EXEC(' SELECT [last name], ( SELECT TOP 1 EDITID from Q2 where Field = ''234'' ) EDITID 
	from Q1	' ) 
END
ELSE
BEGIN
	SELECT [last name],	EDITID  = 0
	from Q1
END

--

Open in new window



VERSION 3

--

IF COL_LENGTH('dbo.Q2','EDITID') IS NOT NULL
BEGIN
	EXEC(' SELECT [last name], ( SELECT TOP 1 EDITID from Q2 where Field = ''234'' ) EDITID 
	from Q1	' ) 
END
ELSE
BEGIN
	SELECT [last name],	EDITID  = 0
	from Q1
END

--

Open in new window

0
 
vbnetcoderAuthor Commented:
mark it isn't scaler

 select EDITID from table2 where Field = '234'

could return several different records.  I need to to know if what is returned by EDITID for the outer query is one of those values and if it is I am going to set the value of the field to 1
0
 
Pawan KumarDatabase ExpertCommented:
Then change your query like below-

you can compare x.EditId ...and change this accordingly..

SELECT [last name], x.EditId
from Q1      
CROSS APPLY
( SELECT EDITID from Q2 where Field = '234' ) x
0
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.