Click here to Skip to main content
15,867,141 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
Hello Every One, I have a problem in retrieving records from SQL Server compact edition database.

I have two tables:-
1. Name= GroupTask , PK=GTaskID

GTaskIDGTaskSubjectGTaskBodyGTaskDeadline
1001Do SomthingBla bla bla...3/31/2011

2.Name=GroupTaskDetails , FK=GTaskID ,UserName

GTaskIDUserName
1001A
1001B
1001C

many rows are there like above...
Now:-
1.
I want to get * from GroupTask table for such tasks on which A,B & C all are
assigned.
2.
I want to get * from GroupTask table for such tasks on which A,B & Z all are assigned.

when I use:- username in('A','B','Z') it returns 1001 but Z is not on 1001
I need AND condition not OR Condition that is what IN predicate does.


Any idea?
Thanks in advance.
Posted
Updated 30-Mar-11 3:21am
v2

1 solution

Modified
SELECT *
FROM GroupTask gt
WHERE EXISTS (SELECT * FROM GroupTaskDetails WHERE GTaskID = gt.GTaskID AND UserName = 'A')
      AND
      EXISTS (SELECT * FROM GroupTaskDetails WHERE GTaskID = gt.GTaskID AND UserName = 'B')
      AND
      EXISTS (SELECT * FROM GroupTaskDetails WHERE GTaskID = gt.GTaskID AND UserName = 'Z')


This will yield the expected result. The only thing I don't know is if subselects are allowed on SQL Server CE.

Cheers,
 
Share this answer
 
v3
Comments
sumit.itsforyou 30-Mar-11 9:28am    
Thanks for Help but actually I need all columns from first table which of course should not be repeated because of primary key.
Above query duplicates result.
and since username(s) will be selected by user from list-box it will lead to large query
one line for each username] I need some efficient way.
I have improved the question, please help.
Thank You.
Manfred Rudolf Bihy 30-Mar-11 9:38am    
I'll fix that statement for you.
Manfred Rudolf Bihy 30-Mar-11 9:42am    
Have a look at the revised solution. This should do the trick..
sumit.itsforyou 30-Mar-11 13:34pm    
Thanks Man, that did the trick, Great Knowledge.
!!CodeProject.com Rocks!!

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