Select IN help !!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select IN help !!!

Author

Topic

gpiva
Starting Member

47 Posts

Posted - 2004-04-21 : 00:22:32

Hi guys I cant find solution for this problem:

I have this table:

ID ICandidateID IAccountingID NAccountingExp
------------------------------------------------------
75 15 1 12
76 15 2 12
93 17 22 12
94 17 25 12
323 67 1 43
324 67 2 43
325 67 3 43
326 67 4 43
327 67 5 43
328 67 6 43
329 67 7 43
330 67 8 43
331 67 9 43
733 112 1 15
832 116 2 15

I want use a select that return me the ICandidateID(s) filtering on
IAccountingID in my case I want the ICandidateID(s) that have IAccountingID 1 and 2

for ex. ICandidateID 15 that have IAccountingID 1 (on ID 75) and IAccountingID 2 (on ID 76)

ID ICandidateID IAccountingID NAccountingExp
------------------------------------------------------
75 15 1 12
76 15 2 12

I dont want the records like ICandidateID 112 and 116

ID ICandidateID IAccountingID NAccountingExp
------------------------------------------------------
733 112 1 15
832 116 2 15

My sql:

SELECT CandidateExp.ICandidateExpID, CandidateExp.ICandidateID, CandidateExp.IAccountingID, CandidateExp.NAccountingExp
FROM CandidateExp
WHERE (((CandidateExp.IAccountingID) In (1,2)));

but the result it will be:

ID ICandidateID IAccountingID NAccountingExp
75 15 1 12
76 15 2 12
323 67 1 43
324 67 2 43
733 112 1 15
832 116 2 15

I dont want to see the last two records...
any help will be appreciate.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-21 : 01:38:13

You can do this in a couple of ways, but here is one:


SELECT CandidateExp.ICandidateExpID, CandidateExp.ICandidateID, CandidateExp.IAccountingID, CandidateExp.NAccountingExp
 FROM CandidateExp
 WHERE ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
 WHERE CandidateExp.IAccountingID = 1)
 AND ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
 WHERE CandidateExp.IAccountingID = 2)

gpiva
Starting Member

47 Posts

Posted - 2004-04-21 : 01:48:08

I try now with my db...
Thank you.

quote:

Originally posted by mohdowais

You can do this in a couple of ways, but here is one:


SELECT CandidateExp.ICandidateExpID, CandidateExp.ICandidateID, CandidateExp.IAccountingID, CandidateExp.NAccountingExp
 FROM CandidateExp
 WHERE ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
 WHERE CandidateExp.IAccountingID = 1)
 AND ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
 WHERE CandidateExp.IAccountingID = 2)

gpiva
Starting Member

47 Posts

Posted - 2004-04-21 : 01:59:03

No I try but 0 records I have to filter only on IAccountingID....

I try to modify the sql that you send me but 0 records too... anyway...

quote:

Originally posted by mohdowais

You can do this in a couple of ways, but here is one:


SELECT CandidateExp.ICandidateExpID, CandidateExp.ICandidateID, CandidateExp.IAccountingID, CandidateExp.NAccountingExp
 FROM CandidateExp
 WHERE ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
 WHERE CandidateExp.IAccountingID = 1)
 AND ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
 WHERE CandidateExp.IAccountingID = 2)

gpiva
Starting Member

47 Posts

Posted - 2004-04-21 : 02:04:58

No sorry again a check the query you sent me it's look correct but 0 records....

quote:

Originally posted by gpiva

I try now with my db...
Thank you.

quote:

Originally posted by mohdowais

You can do this in a couple of ways, but here is one:


SELECT CandidateExp.ICandidateExpID, CandidateExp.ICandidateID, CandidateExp.IAccountingID, CandidateExp.NAccountingExp
 FROM CandidateExp
 WHERE ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
 WHERE CandidateExp.IAccountingID = 1)
 AND ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
 WHERE CandidateExp.IAccountingID = 2)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-21 : 02:42:07

Try adding the condition to the WHERE clause:



SELECT CandidateExp.ICandidateExpID, CandidateExp.ICandidateID, CandidateExp.IAccountingID, CandidateExp.NAccountingExp
 FROM CandidateExp
 WHERE ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
     WHERE CandidateExp.IAccountingID = 1)
 AND ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
     WHERE CandidateExp.IAccountingID = 2)
  AND IAccountingID IN(1, 2)

Alternatively you try this too:


SELECT CandidateExp.ICandidateExpID, CandidateExp.ICandidateID, CandidateExp.IAccountingID, CandidateExp.NAccountingExp 
 FROM CandidateExp INNER JOIN
 (SELECT ICandidateID FROM CandidateExp WHERE IAccountingID IN (1, 2) GROUP BY ICandidateID
  HAVING COUNT(*)=2) b
 ON CandidateExp.ICandidateID = b.ICandidateID AND IAccountingID IN (1, 2)

gpiva
Starting Member

47 Posts

Posted - 2004-04-21 : 22:26:13

Thank you
I fixed the problem with the HAVING COUNT(*)=2) version you suggest me!

quote:

Originally posted by mohdowais

Try adding the condition to the WHERE clause:



SELECT CandidateExp.ICandidateExpID, CandidateExp.ICandidateID, CandidateExp.IAccountingID, CandidateExp.NAccountingExp
 FROM CandidateExp
 WHERE ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
     WHERE CandidateExp.IAccountingID = 1)
 AND ICandidateExpID 
  IN(SELECT ICandidateExpID FROM CandidateExp
     WHERE CandidateExp.IAccountingID = 2)
  AND IAccountingID IN(1, 2)

Alternatively you try this too:


SELECT CandidateExp.ICandidateExpID, CandidateExp.ICandidateID, CandidateExp.IAccountingID, CandidateExp.NAccountingExp 
 FROM CandidateExp INNER JOIN
 (SELECT ICandidateID FROM CandidateExp WHERE IAccountingID IN (1, 2) GROUP BY ICandidateID
  HAVING COUNT(*)=2) b
 ON CandidateExp.ICandidateID = b.ICandidateID AND IAccountingID IN (1, 2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources