Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server database
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 30-Mar-11 2:37am
Edited 30-Mar-11 3:21am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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,
  Permalink  
v3
Comments
sumit.itsforyou at 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 R. Bihy at 30-Mar-11 9:38am
   
I'll fix that statement for you.
Manfred R. Bihy at 30-Mar-11 9:42am
   
Have a look at the revised solution. This should do the trick..
sumit.itsforyou at 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)

  Print Answers RSS
0 CHill60 185
1 Sarvesh Kumar Gupta 185
2 OriginalGriff 179
3 George Jonsson 154
4 ArunRajendra 144


Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 30 Mar 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid