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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
ty
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.