Click here to Skip to main content
12,244,664 members (65,358 online)
Rate this:
 
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 3:37am
Edited 30-Mar-11 4: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 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 30-Mar-11 9:38am
   
I'll fix that statement for you.
Manfred R. 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160426.1 | Last Updated 30 Mar 2011
Copyright © CodeProject, 1999-2016
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