Should be an easy select, shouldn't it

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)

Should be an easy select, shouldn't it

Author

Topic

hawkpie
Starting Member

3 Posts

Posted - 2005-09-29 : 04:17:55

Sorry if this is an easy one, but it just has me stumped.

I have a table with 2 columns, here's a quick snapshot of the data:

applicantid          skillid
387                        1
387                        2
387                        5
387                        7
388                        1
388                        2
388                        3
388                        5
388                        7

I need to write a Select statement that selects all applicant ids for, say, skillids 1 and 3. But they must have them BOTH.

Can't use IN and OR, as this selects people who have at least one of them.

Any pointers would be appreciated.

Cheers
pie

mr_mist
Grunnio

1870 Posts

Posted - 2005-09-29 : 04:23:28

There's various ways of doing this, one of them might be

SELECT applicantid FROM yourtable WHERE skillid = 1 AND applicantid IN (SELECT applicantid FROM yourtable WHERE skillid = 3)

-------
Moo. :)

hawkpie
Starting Member

3 Posts

Posted - 2005-09-29 : 04:36:55

Cheers Moo

That would work great for 2 Ids, but forgot to mention that there could be none, 1 or up to 8.

The skills requried are chosen by the user, depending on what they are after.

ta
pie

iminore
Posting Yak Master

141 Posts

Posted - 2005-09-29 : 04:46:35

You could use a temporary table. First create a temporary table of all users matching your first skill id. Then delete from the temprary table those that don't meet successive skill ids. Finally read the temporary table for your results.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-09-29 : 04:48:01

Relational division...

http://www.dbazine.com/ofinterest/oi-articles/celko1

DavidM

A front-end is something that tries to violate a back-end.

hawkpie
Starting Member

3 Posts

Posted - 2005-09-29 : 05:04:10

hi guys

cheers for the replies. i got the answer on another forum and here it is if you are interested.

select applicantid
from myTable
where skillid in (1, 3)
group by applicantid
having count(skillid) = 2

cheers again
pie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources