none
Self reference to the table issue-Query required

    Question

  • Hi,

    I have the below query which will give me the result as

    select DC.Fullname as Fullname, DSL.Skills as Skills, AdminSC.SkillRatingID as Rating
                        from dim_Contacts DC
                        inner join admin_skills AdminSC on DC.attid = AdminSC.attid
                        inner join dim_Skill DSL on DSL.ID = AdminSC.skillID
                        inner join dim_Pattern DPS on DPS.SkillID = DSL.ID
                        where DPS.PatternTypeID = '2'

    Fullname Skills Rating
    Rahul BO Universe 2
    Kiran Crystal Reports / Webi 4

    Now what I am looking for is,I need to get 2 more rows with null values(eg :Rahul-Crystal Reports/Webi-0).Please find the required result set.

    Fullname Skills Rating
    Rahul BO Universe 2
    Kiran Crystal Reports / Webi 4
    Rahul Crystal Reports / Webi 0
    Kiran BO Universe 0

    Please help me in providing the query.Thanks

    Wednesday, March 05, 2014 12:18 PM

Answers

  • You can use LEFT OUTER JOIN with Skills Table like follow query:

    SELECT  DC.Fullname AS Fullname ,
            DSL.Skills AS Skills ,
            AdminSC.SkillRatingID AS Rating
    FROM    dim_Contacts DC
            INNER JOIN admin_skills AdminSC ON DC.attid = AdminSC.attid
            LEFT OUTER JOIN dim_Skill DSL ON DSL.ID = AdminSC.skillID
            INNER JOIN dim_Pattern DPS ON DPS.SkillID = DSL.ID
    WHERE   DPS.PatternTypeID = '2'
    


    sqldevelop.wordpress.com

    Wednesday, March 05, 2014 1:24 PM