quote:

---

Originally posted by rajdaksha

Hi

you can pass the WHERE condition both the field...Values

-------------------------
R..

---

quote:

---

Originally posted by rajdaksha

Hi


Select propertId from tableName where type = 'Correct' and Name = 'Two'

-------------------------
R..

---

quote:

---

Originally posted by ayamas

Select * from

(
select * from yourtable where type='CORRECT'

)T where ID='TWO'

---

DECLARE @searchString NVARCHAR(255)
SET @searchString = 'TWO'

DECLARE @foo TABLE (
	[propertyID] INT
	, [Name] NVARCHAR(255)
	, [Type] NVARCHAR(255)
	)

INSERT @foo
      SELECT 550, 'ONE', 'CORRECT'
UNION SELECT 550, 'TWO', 'CORRECT'
UNION SELECT 551, 'TWO', 'CORRECT'
UNION SELECT 552, 'ONE', 'WRONG'

SELECT
	*
FROM
	@foo f
WHERE
	f.[type] = 'CORRECT'
	AND f.[name] = @searchString
	AND NOT EXISTS (
		SELECT 1
		FROM @foo f2
		WHERE
			f2.[type] = 'CORRECT'
			AND f2.[name] <> @searchString
			AND f2.[propertyId] = f.[propertyId]
		)

quote:

---

Originally posted by Velnias

quote:

---

Originally posted by ayamas

Select * from

(
select * from yourtable where type='CORRECT'

)T where ID='TWO'

---

This did not work

I tried


SELECT * FROM (SELECT *
FROM tblCharges
WHERE (propertyType = 'Sales')) AS T
WHERE (description = 'home charge')

And then I got for example a bunch of Ids which was fine

SELECT *
FROM tblCharges
WHERE (propertyId = 382)

I got a lot of instances for this propertyId.

I only want the Ids where whatever I type in decription, that the only instances returned are ids which have this description and not any other descriptions.

---

declare @tbl as table(Property int, ID varchar(5), type varchar(10))
insert into @tbl
select 550, 'ONE', 'CORRECT' union all
select 550, 'TWO', 'CORRECT' union all
select 551, 'TWO', 'CORRECT' union all
select 552, 'ONE', 'WRONG'
select * from @tbl

Select * from

(
select * from @tbl where type='CORRECT'

)T where ID='TWO'

550
551

quote:

---

Originally posted by Transact Charlie

ayamas,

You get id's

550
551

from your query which is wrong.

OP *only* wants id's where there is NO OTHER description as well as the one he is looking for. I believe the only value he wants is 551 as there is no row with NAME=ONE and TYPE=CORRECT for 551 and there is for 550.

Correct Velnias?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

Originally posted by Transact Charlie

Either Madhi's or my suggestion should work then.

I think the NOT EXISTS method has better performance but either will probably be good enough.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

Originally posted by Transact Charlie



I think the NOT EXISTS method has better performance but either will probably be good enough.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

Originally posted by Velnias

quote:

---

Originally posted by Transact Charlie

Either Madhi's or my suggestion should work then.

I think the NOT EXISTS method has better performance but either will probably be good enough.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

Hi Charlie

I can't seem to understand your query in the searchString do I have to search for the description I want to search for or not to ?

---

DECLARE @searchString NVARCHAR(255)    SET @searchString = 'TWO'
DECLARE @excludeString NVARCHAR(255)   SET @excludeString = 'ONE'

DECLARE @foo TABLE (
	[propertyID] INT
	, [Name] NVARCHAR(255)
	, [Type] NVARCHAR(255)
	)

INSERT @foo
      SELECT 550, 'ONE', 'CORRECT'
UNION SELECT 550, 'TWO', 'CORRECT'
UNION SELECT 551, 'TWO', 'CORRECT'
UNION SELECT 552, 'ONE', 'WRONG'

SELECT
	*
FROM
	@foo f
WHERE
	f.[type] = 'CORRECT'
	AND f.[name] = @searchString
	AND NOT EXISTS (
		SELECT 1
		FROM @foo f2
		WHERE
			f2.[type] = 'CORRECT'
			AND f2.[name] = @excludeString
			AND f2.[propertyId] = f.[propertyId]
		)