insert into tblLicense (ID, OwnerID)
select @ID, stringval
from   CSVTable(@OwnerID) t
where  not exists (select * from tblLicense x where x.OwnerID = t.stringval)

declare @table table
(
	ID 	varchar(4),
	OwnerID	varchar(6)
)

insert into @table
select	'ID01', 'PId_01'	union all
select	'ID01', 'PId_02'	union all
select	'ID01', 'PId_03'	union all
select	'ID02', 'PId_02'	union all
select	'ID02', 'PId_03'	union all
select	'ID03', 'PId_04'	union all
select	'ID03', 'PId_06'

declare @OwnerList 	varchar(100),
	@OwnerCnt	int

select	@OwnerList = 'PId_01,PId_02,PId_03'

select	@OwnerCnt = count(*)
from	dbo.CSVTable(@OwnerList)

select	t.ID
from	@table t inner join dbo.CSVTable(@OwnerList) o
	on	t.OwnerID	= o.stringval
group by t.ID
having count(*) = @OwnerCnt

quote:

---

Originally posted by NeilG

So to clarify on this you are looking for something simlar to an 'in' operator in a where clause to only output the ID if all of the Owners_id are passed to the sp

---

quote:

---

Originally posted by Peso

IF NOT EXISTS (SELECT * FROM Table WHERE ID = @ID AND OwnerID = @OwnerID)
INSERT Table (ID, OwnerID) VALUES (@ID, @OwnerID)



Peter Larsson
Helsingborg, Sweden

---

quote:

---

Say for example in above table,
We have ownersID "PId_01,PId_02,PId_03" for ID "ID01" and owners IDs "PId_02,PId_03 " for ID "ID02"
so now say when user try to Add owner "PId_01" under "ID02" ,we should not allow user to insert,bcoz it makes two IDs having same set of OwnersID.So Before inserting we have check whether any ID exists which contain exact set of OwnersID.
If preset,don;t allow to insert,just return the ID which have same set of values.
IF doesn't exists ,then allow it insert and returns ID to which new Owner ID "PId_01" is allowed to added.

---

quote:

---

Now i don;t want to insert at this point..
I just want to retrieve ID value if any ID has/contain exact set of OwnersID ,which is pased from front end.

---

-- Prepare sample data
DECLARE	@License TABLE (ID VARCHAR(5), OwnersID VARCHAR(6))

INSERT	@License
SELECT	'ID_01', 'PId_01' UNION ALL
SELECT	'ID_01', 'PId_02' UNION ALL
SELECT	'ID_01', 'PId_03' UNION ALL
SELECT	'ID_02', 'PId_01' UNION ALL
SELECT	'ID_02', 'PId_02'

DECLARE	@UserPass VARCHAR(100)

-- Show the first expected output
SET	@UserPass = 'PId_01,PId_02,PId_03'

SELECT		l.ID
FROM		@License AS l
LEFT JOIN	@License AS up ON ',' + @UserPass + ',' LIKE '%,' + l.OwnersID + ',%'
GROUP BY	l.ID
HAVING		COUNT(DISTINCT l.OwnersID) = 1 + DATALENGTH(@UserPass) - DATALENGTH(REPLACE(@UserPass, ',', ''))

-- Show the second expected output
SET	@UserPass = 'PId_01,PId_02'

SELECT		l.ID
FROM		@License AS l
LEFT JOIN	@License AS up ON ',' + @UserPass + ',' LIKE '%,' + l.OwnersID + ',%'
GROUP BY	l.ID
HAVING		COUNT(DISTINCT l.OwnersID) = 1 + DATALENGTH(@UserPass) - DATALENGTH(REPLACE(@UserPass, ',', ''))

quote:

---

Originally posted by khtan

I think you are confusing everybody with what you want.

Your initial requirement is for inserting record.

quote:

---

Say for example in above table,
We have ownersID "PId_01,PId_02,PId_03" for ID "ID01" and owners IDs "PId_02,PId_03 " for ID "ID02"
so now say when user try to Add owner "PId_01" under "ID02" ,we should not allow user to insert,bcoz it makes two IDs having same set of OwnersID.So Before inserting we have check whether any ID exists which contain exact set of OwnersID.
If preset,don;t allow to insert,just return the ID which have same set of values.
IF doesn't exists ,then allow it insert and returns ID to which new Owner ID "PId_01" is allowed to added.

---

But later, you change that and wanted query to return the IDs.

quote:

---

Now i don;t want to insert at this point..
I just want to retrieve ID value if any ID has/contain exact set of OwnersID ,which is pased from front end.

---

I am not sure what do you want now.

Maybe you should rephrase your question and requirement on what do you need NOW. If any solution posted does meet your requirement, please says so.

And when posting your requirement, please also post the table DDL, some sample data and the expected result. Also explain any business logic etc.

---

KH

---

quote:

---

Originally posted by khtan

2 things here.
1. you need CSVTable from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable to split the string 'PId_01,PId_02,PId_05' to 3 rows

2. check for existence of Owner ID during insert

something like

insert into tblLicense (ID, OwnerID)
select @ID, stringval
from   CSVTable(@OwnerID) t
where  not exists (select * from tblLicense x where x.OwnerID = t.stringval)

@ID, @OwnerID are the variable pass in from front end

EDIT : You can also use fnParseString from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 to parse the string 'PId_01,PId_02,PId_05'

---

KH

---