In attachment there is sql script with resulting query. I think example is very clear.
Skillset has skills and user has skills. I need to find out which users are appropriate for skillSets. Example: I need to find out skillSets for user_1. user_1 have skill_1 and skill_2. Then I have skillSets: skillsetA, which requires skill_1 skillsetB, which requires skill_1 and skill_2 skillsetC, which requires skill_1 and skill_2 and skill_3 skillsetD, which requires skill_3 skillsetE, which has no skill defined Now I need to get all skillSets which are appropriate for the user. In this example, appropriate skillSets for a user_1 are skillsetA, skillsetB and skillsetE. How can I do that? I have nHibernate, version 3.3. I have skill, skillset and user object, the same as tables are. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
IF EXISTS(SELECT * FROM sys.tables AS t WHERE t.name='Skill') DROP TABLE [dbo].[Skill]; GO IF EXISTS(SELECT * FROM sys.tables AS t WHERE t.name='SkillSet') DROP TABLE [dbo].[SkillSet]; GO IF EXISTS(SELECT * FROM sys.tables AS t WHERE t.name='SkillSet_Skill') DROP TABLE [dbo].[SkillSet_Skill]; GO IF EXISTS(SELECT * FROM sys.tables AS t WHERE t.name='User') DROP TABLE [dbo].[User]; GO IF EXISTS(SELECT * FROM sys.tables AS t WHERE t.name='User_Skill') DROP TABLE [dbo].[User_Skill]; GO CREATE TABLE [dbo].[Skill]( [skill_id] [int] NOT NULL, [skill_name] [varchar](50) NOT NULL CONSTRAINT [PK_Skill] PRIMARY KEY CLUSTERED ([skill_id] ASC)) ON [PRIMARY]; GO CREATE TABLE [dbo].[SkillSet]( [skillSet_id] [int] NOT NULL, [skillSet_name] [varchar](50) NOT NULL CONSTRAINT [PK_SkillSet] PRIMARY KEY CLUSTERED ([skillSet_id] ASC)) ON [PRIMARY]; GO CREATE TABLE [dbo].[SkillSet_Skill]( [skillSet_id] [int] NOT NULL, [skill_id] [int] NOT NULL, CONSTRAINT [PK_SkillSet_Skill] PRIMARY KEY CLUSTERED ([skillSet_id] ASC, [skill_id] ASC)) ON [PRIMARY]; GO CREATE TABLE [dbo].[User]( [usr_id] [int] NOT NULL, [usr_name] [varchar](50) NOT NULL CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([usr_id] ASC)) ON [PRIMARY]; GO CREATE TABLE [dbo].[User_Skill]( [usr_id] [int] NOT NULL, [skill_id] [int] NOT NULL, CONSTRAINT [PK_User_Skill] PRIMARY KEY CLUSTERED ([usr_id] ASC, [skill_id] ASC)) ON [PRIMARY]; GO INSERT INTO dbo.Skill ( skill_id, skill_name ) VALUES ( 1, 'skill_1'), ( 2, 'skill_2'), ( 3, 'skill_3'); INSERT INTO dbo.SkillSet ( skillSet_id, skillSet_name ) VALUES ( 1, 'skillsetA'), ( 2, 'skillsetB'), ( 3, 'skillsetC'), ( 4, 'skillsetD'), ( 5, 'skillsetE'); INSERT INTO dbo.SkillSet_Skill ( skillSet_id, skill_id ) VALUES ( 1, 1), ( 2, 1), ( 2, 2), ( 3, 1), ( 3, 2), ( 3, 3), ( 4, 3); INSERT INTO dbo.[User] ( usr_id, usr_name ) VALUES ( 1, 'user_1'); INSERT INTO dbo.User_Skill ( usr_id, skill_id ) VALUES ( 1, 1), (1,2); GO ;WITH CTE AS( SELECT sss.skillSet_id, us.skill_id FROM dbo.SkillSet_Skill AS sss LEFT JOIN dbo.User_Skill AS us ON us.skill_id = sss.skill_id )SELECT c.skillSet_id FROM CTE c WHERE NOT EXISTS(SELECT * FROM CTE WHERE CTE.skillSet_id=c.skillSet_id AND CTE.skill_id IS NULL) GROUP BY c.skillSet_id UNION ALL SELECT ss.skillSet_id FROM dbo.SkillSet AS ss LEFT JOIN dbo.SkillSet_Skill AS sss ON sss.skillSet_id = ss.skillSet_id WHERE sss.skillSet_id IS NULL;
