Q

Query to return all 5 SKILL_IDs

I'm trying to write a query. I have this query which returns 3 rows of data:

 

 select SKILL_ID from AGENT_SKILL WHERE AGENT_ID = '123';

I have another table called SKILL which is linked to the AGENT_SKILL table through SKILL_ID. The same SKILL_ID can exist more than once in the AGENT_SKILL table since different agents may have the same skill. So the SKILL table has 5 SKILL_ID's and the above query returns 3 of those SKILL_ID's from the AGENT_SKILL table. So I need to get the remaining 2 SKILL_ID's from the SKILL table. Bascially, I have a select box that is populated with the skills that the agent already has (based on the above query). I now want the 'available skills' select box to populated only with the skills that are left over. How can I get this to work?

Here's a query that I've been trying so far:

 

 select SKILL.SKILL_NAME FROM SKILL JOIN AGENT_SKILL ON SKILL.SKILL_ID = AGENT_SKILL.SKILL_ID WHERE AGENT_SKILL.AGENT_ID <> '123';

This query seems only to return all SKILL_ID's from the SKILL table rather than just the ones that the agent doesn't already have. Any help would be greatly appreciated.

Hello, my friend. This is what you need to do to resolve your problem. Good luck:

 

 SELECT skill.skill_id, skill_name FROM skill WHERE NOT EXISTS (SELECT * FROM agent_skill WHERE skill.skill_ID = agent_skill.skill_ID AND agent_id='123')

 

For More Information

This was first published in April 2001

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close