Click here to Skip to main content
15,943,103 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have one table name ContactBook, which contain two fields ContactID and ItemID. Here am associating ContactId to ItemID, i,e one contactid is associating with more than one item. In sql server i want to fetch associated contactid if i give item id,

means
if i give item id 1,2,3,then i want to fetch contactid which is associated with 1 and 2 and 3, if any contact is associated with 1 or 2 it should not come.


Plz give me proper suggestions
Posted
Comments
Zoltán Zörgő 17-Jan-13 6:47am    
Vague and unclear. Are there any other tables involved in this than ContactBook? Is ContactBook.ContactID primary key and ContactBook.ItemID is foreign key and it is referencing the same table ContactBook.ContactID? Give a more concrete and complex, example with all relevant fields in the source and desired result.

1 solution

hi,

you can plan like -
SQL
begin
declare @id1 varchar(50)=3
declare @id2 varchar(50)=0
declare @id3 varchar(50)=0
declare @id4 varchar(50)=0
declare @id5 varchar(50)=0
declare @counter int=0
begin
if(@id1!=0)
set @counter=1

if(@id1!=0 and @id2!=0)
set @counter=2

if(@id1!=0 and @id2!=0 and @id3!=0)
set @counter=3

if(@id1!=0 and @id2!=0 and @id3!=0 and @id4!=0)
set @counter=4

if(@id1!=0 and @id2!=0 and @id3!=0 and @id4!=0 and @id5!=0)
set @counter=5

if(@counter=0)
begin
select taskid
            from tbltask where updatedby=0
end
else if (@counter=1)
begin
select taskid
            from tbltask
            where updatedby=@id1
            end

else if (@counter=2)
begin
select taskid
            from tbltask
            where updatedby=@id1
            and updatedby=@id2
            end
else if (@counter=3)
begin
select taskid
            from tbltask
            where updatedby=@id1
            and updatedby=@id2
            and updatedby=@id3
            end
else if (@counter=4)
begin
select taskid
            from tbltask
            where updatedby=@id1
            and updatedby=@id2
            and updatedby=@id3
            and updatedby=@id4
            end
else if (@counter=5)
begin
select taskid
            from tbltask
            where updatedby=@id1
            and updatedby=@id2
            and updatedby=@id3
            and updatedby=@id4
            and updatedby=@id5
            end
        end
end
 
Share this answer
 
Comments
Zoltán Zörgő 17-Jan-13 8:05am    
One question: and what if there are 125543 IDs?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900