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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top