Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: