nested select query - SQL Server Forums

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

SQL Server Development (2000)

nested select query

Author

Topic

vijaychaudhari_engg
Starting Member

5 Posts

Posted - 2008-08-11 : 08:36:27

select pkgrowid_ from csmsiproperty where property<> (select distinct property from csmsiproperty where property<>'configdesctext' and value='')
here i am trying to get rowid of a column property where proeprty value is not equal to configdesctext.but i am getting subquery return more than one value error .how to solve this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-11 : 08:40:53

select pkgrowid_ from csmsiproperty where property not in (select distinct property from csmsiproperty where property<>'configdesctext' and value='')

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 08:56:00

[code]SELECT [pkgrowid_]
FROM csmsiProperty
WHERE [Property] = 'configdesctext'[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-11 : 08:58:49

quote:
Originally posted by Peso

SELECT [pkgrowid_]
FROM csmsiProperty
WHERE [Property] = 'configdesctext'

E 12°55'05.25"
N 56°04'39.16"

Good one. I didnt see the full query

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 09:04:21

Thanks.

I thought so too, until I noticed some special cases where it will not work.
Your query, and this below, will work

DECLARE	@Sample TABLE (RowID INT IDENTITY, Col1 VARCHAR(20), Col2 VARCHAR(20))

INSERT	@Sample
SELECT	'configdesctext', '' UNION ALL
SELECT	'configdesctext', '@' UNION ALL
SELECT	'peso', '' UNION ALL
SELECT	'peso', '@' UNION ALL
SELECT	'x', '' UNION ALL
SELECT	'yak', '@'

-- Madhi
SELECT	s.*
FROM	@Sample AS s
WHERE	s.Col1 NOT IN (SELECT DISTINCT x.Col1 FROM @Sample AS x WHERE x.Col1 <> 'configdesctext' AND x.Col2 = '')

-- Peso
SELECT		s.*
FROM		@Sample AS s
LEFT JOIN	@Sample AS x ON x.Col1 = s.Col1
			AND x.Col1 <> 'configdesctext'
			AND x.Col2 = ''
WHERE		x.Col1 IS NULL

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources