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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL Server guru is waiting to answer your toughest questions.
This was first published in April 2001