Can you re-query a query ?

[Collin]

I have a table with lets say two fields, ProjectID and FullName. The FullName can have the same ProjectId number.<br>
What I want to do is make a query that will tell me if any given FullNames have worked on the same ProjectId<br>
So far I have<br>
<br>
SELECT ProjectID, FullName FROM MyTable WHERE<br>
FullName = SomeName1 OR FullName = SomeName2<br>
<br>
Which gives me the all ProjectID's that both the SomeNames have worked on, but what I need is a query returning only the ProjectID's that both SomeNames have worked on<br>
Thanks<br>
Collin<br>
Oh yes, can you requery a query in buffer memory?

 

[Shobin]

If you use the Keyword DISTINCT on your select and don't put the field FullName, you'll have the ProjectID of both SomeNames.<br>
<br>
About requerying i don't know anything about it...<br>
<br>
Good Luck

 

[Collin]

Thanks, but, I didnt field my question very well, let me try again, I want to get all the ProjectId's that the user inputed variable ; SomeName1 and SomeName2 have worked on, then get the ProjectId's that are common to both SomeName1 and SomeName2, can you help me with that?, I think I have a good grasp on the SELECT DISTINCT thing, but still cant seem to get the results Iam after.<br>
<br>
Thanks<br>
Collin

 

[calahans]

Hi Collin,<br>
<br>
Try the following code to get the common projects:<br>
<br>
SELECT PROJECTID FROM MYTABLE<br>
WHERE PROJECTID IN (SELECT PROJECTID FROM MYTABLE<br>
WHERE FULLNAME=SOMENAME1) AND PROJECTID IN (SELECT DISTINCT<br>
PROJECTID FROM MYTABLE WHERE FULLNAME=SOMENAME2)<br>
<br>
The first subselect gets the project for name 1 and the second subselect gets the project for somename2. Then we get the commons ones from each resultset. I think it works.<br>
<br>
As for requerying a query in buffer, just call the query again. It should still be in the buffer unless the DBMS has cleared it out to free up buffers.<br>
<br>
C

 

[kalyan]

Hi,<br>
You can try something simpler.<br>
<br>
Select projectid from mytable where fullname = someone1<br>
INTERSECT<br>
Select projectid from mytable where fullname = someone2;<br>
<br>
Good Luck<br>
Kalyan<br>

 

[calahans]

Thanks kalyan - I must agree, that is much simpler - I wasn't aware of the intersect keyword. <br>
<br>
C