Click here to Skip to main content
11,704,472 members (55,687 online)
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 Sergey Alexandrovich Kryukov 740
1 OriginalGriff 495
2 Maciej Los 355
3 Andy Lanng 284
4 CPallini 267
0 OriginalGriff 8,824
1 Sergey Alexandrovich Kryukov 8,247
2 CPallini 5,197
3 Maciej Los 4,726
4 Mika Wendelius 3,606


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100