I have three table Projects, Skills and ProjectSkills to obtain the Skills for a certain Project I write this SQL-query: 

SELECT {Skills}.[SkillName]
FROM {Skills}
  left JOIN {ProjectsSkills} ON {ProjectsSkills}.[SkillId] = {Skills}.[SkillId]
  left JOIN {Projects} ON {Projects}.[ProjectId] = {ProjectsSkills}.[ProjectId]
WHERE {ProjectsSkills}.[ProjectId] = @ProjectId 

But how I change this SQL-query to obtain the opposit? All Skills that not belongs to a certain project.

Hi Danny,

Please try the below mentioned changes

SELECT {Skills}.[SkillName]
FROM {Skills}
  left JOIN {ProjectsSkills} ON {ProjectsSkills}.[SkillId] = {Skills}.[SkillId]
  left JOIN {Projects} ON {Projects}.[ProjectId] = {ProjectsSkills}.[ProjectId]
WHERE {ProjectsSkills}.[ProjectId] <> @ProjectId 


{ProjectsSkills}.[ProjectId] <> @ProjectId 

Use <> (not equal to)


Hope this helps you!


Regards,

Benjith Sam

Benjith Sam wrote:

Hi Danny,

Please try the below mentioned changes

SELECT {Skills}.[SkillName]
FROM {Skills}
  left JOIN {ProjectsSkills} ON {ProjectsSkills}.[SkillId] = {Skills}.[SkillId]
  left JOIN {Projects} ON {Projects}.[ProjectId] = {ProjectsSkills}.[ProjectId]
WHERE {ProjectsSkills}.[ProjectId] <> @ProjectId 


{ProjectsSkills}.[ProjectId] <> @ProjectId 

Use <> (not equal to)


Hope this helps you!


Regards,

Benjith Sam

 Hi Benith this is not the solution. Because then you get all the skills for the other projects. But I want the not used skills for the certain project. This is normally the solution but I get a syntax error. 

SELECT {Skills}.[SkillName] 
FROM {Skills}
WHERE SELECT {Skills}.[SkillName] NOT IN (
FROM {Skills}
  left JOIN {ProjectsSkills} ON {ProjectsSkills}.[SkillId] = {Skills}.[SkillId]
  left JOIN {Projects} ON {Projects}.[ProjectId] = {ProjectsSkills}.[ProjectId]
WHERE {ProjectsSkills}.[ProjectId] = @ProjectId)

 

Hi Danny,

Please try this:

SELECT {Skills}.[SkillName] 
FROM {Skills}
WHERE {Skills}.[SkillName] NOT IN (
SELECT {Skills}.[SkillName]
FROM {Skills}
  left JOIN {ProjectsSkills} ON {ProjectsSkills}.[SkillId] = {Skills}.[SkillId]
  left JOIN {Projects} ON {Projects}.[ProjectId] = {ProjectsSkills}.[ProjectId]
WHERE {ProjectsSkills}.[ProjectId] = @ProjectId)


Let me know if it worked for you.


Kind regards,

Rui Barradas

Rui Barradas wrote:

Hi Danny,

Please try this:

SELECT {Skills}.[SkillName] 
FROM {Skills}
WHERE {Skills}.[SkillName] NOT IN (
SELECT {Skills}.[SkillName]
FROM {Skills}
  left JOIN {ProjectsSkills} ON {ProjectsSkills}.[SkillId] = {Skills}.[SkillId]
  left JOIN {Projects} ON {Projects}.[ProjectId] = {ProjectsSkills}.[ProjectId]
WHERE {ProjectsSkills}.[ProjectId] = @ProjectId)


You can also use the NOT EXISTS clause:

SELECT {Skills}.[SkillName] 
FROM {Skills}
WHERE NOT EXISTS (
SELECT 1
FROM {Skills}
  left JOIN {ProjectsSkills} ON {ProjectsSkills}.[SkillId] = {Skills}.[SkillId]
  left JOIN {Projects} ON {Projects}.[ProjectId] = {ProjectsSkills}.[ProjectId]
WHERE {ProjectsSkills}.[ProjectId] = @ProjectId)


Let me know if it worked for you.


Kind regards,

Rui Barradas

 Normally this should work. But I get this.

 

Hello Danny,

That's very strange.

Can you send us some print of your SQL query in Service Studio?


Kind regards,

Rui Barradas

Rui Barradas wrote:

Hello Danny,

That's very strange.

The error description refers to a "skillmania" word, which doesn't exist in the query.

Can you send us some print of your SQL query in Service Studio?


Kind regards,

Rui Barradas

 skillmania is the application name. It's a very strange translation of the SQL.

 

Solution

Hello Danny,

You have an extra 'SELECT' after the first 'WHERE' condition. You need to remove it.

Please try this:

SELECT {Skills}.[SkillName] 
FROM {Skills}
WHERE {Skills}.[SkillName] NOT IN (
SELECT {Skills}.[SkillName]
FROM {Skills}
  left JOIN {ProjectsSkills} ON {ProjectsSkills}.[SkillId] = {Skills}.[SkillId]
  left JOIN {Projects} ON {Projects}.[ProjectId] = {ProjectsSkills}.[ProjectId]
WHERE {ProjectsSkills}.[ProjectId] = @ProjectId)


Kind regards,

Rui Barradas

Solution

Rui Barradas wrote:

Hello Danny,

You have an extra 'SELECT' after the first 'WHERE' condition. You need to remove it.

Please try this:

SELECT {Skills}.[SkillName] 
FROM {Skills}
WHERE {Skills}.[SkillName] NOT IN (
SELECT {Skills}.[SkillName]
FROM {Skills}
  left JOIN {ProjectsSkills} ON {ProjectsSkills}.[SkillId] = {Skills}.[SkillId]
  left JOIN {Projects} ON {Projects}.[ProjectId] = {ProjectsSkills}.[ProjectId]
WHERE {ProjectsSkills}.[ProjectId] = @ProjectId)


Kind regards,

Rui Barradas

 That's it. Thanks Rui

 

Hello Danny,


I'm glad that you managed to solve it :)

I guess that you marked your own reply as a solution by mistake.

Do you mind to mark my answer so other forum members can benefit with it?


Kind regards,

Rui Barradas