CODEWITHSUNDEEP
sqljoin
Aaina
Aaina

Reputation: 1

How to write a sql query to do multiple joins with the same table

I am trying to write a SQL query to fetch data from two tables. One table A has 9 fields which are primary key relation to table B. 

SELECT JBO8.posid
    , JbO8.actnm
    , JBO8.prntposid
    , JBO8.lngcd
    , JBO8.H0
    , JBO8.h1
    , JBO8.h2
    , JBO8.h3
    , JBO8.h4
    , JBO8.h5
    , JBO8.H6
    , JBO8.H7
    , JBO8.H8
    , organization.name AS H9
FROM (
    SELECT JBO7.posid
        , JbO7.actnm
        , JBO7.prntposid
        , JBO7.lngcd
        , JBO7.H0
        , JBO7.h1
        , JBO7.h2
        , JBO7.h3
        , JBO7.h4
        , JBO7.h5
        , JBO7.H6
        , JBO7.H7
        , organization.name AS H8
        , JBO7.hierarchylvl9
    FROM (
        SELECT JBO6.posid
            , JbO6.actnm
            , JBO6.prntposid
            , JBO6.lngcd
            , JBO6.H0
            , JBO6.h1
            , JBO6.h2
            , JBO6.h3
            , JBO6.h4
            , JBO6.h5
            , JBO6.H6
            , organization.name AS H7
            , JBO6.hierarchylvl8
            , JBO6.hierarchylvl9
        FROM (
            SELECT JBO5.posid
                , JbO5.actnm
                , JBO5.prntposid
                , JBO5.lngcd
                , JBO5.H0
                , JBO5.h1
                , JBO5.h2
                , JBO5.h3
                , JBO5.h4
                , JBO5.h5
                , organization.name AS H6
                , JBO5.hierarchylvl7
                , JBO5.hierarchylvl8
                , JBO5.hierarchylvl9
            FROM (
                SELECT JBO4.posid
                    , JbO4.actnm
                    , JBO4.prntposid
                    , JBO4.lngcd
                    , JBO4.H0
                    , JBO4.h1
                    , JBO4.h2
                    , JBO4.h3
                    , JBO4.h4
                    , organization.name AS H5
                    , JBO4.hierarchylvl6
                    , JBO4.hierarchylvl7
                    , JBO4.hierarchylvl8
                    , JBO4.hierarchylvl9
                FROM (
                    SELECT JBO3.posid
                        , JbO3.actnm
                        , JBO3.prntposid
                        , JBO3.lngcd
                        , JBO3.H0
                        , JBO3.h1
                        , JBO3.h2
                        , JBO3.h3
                        , organization.name AS H4
                        , JBO3.hierarchylvl5
                        , JBO3.hierarchylvl6
                        , JBO3.hierarchylvl7
                        , JBO3.hierarchylvl8
                        , JBO3.hierarchylvl9
                    FROM (
                        SELECT JBO2.posid
                            , JbO2.actnm
                            , JBO2.prntposid
                            , JBO2.lngcd
                            , JBO2.H0
                            , JBO2.h1
                            , JBO2.h2
                            , organization.name AS H3
                            , JBO2.hierarchylvl4
                            , JBO2.hierarchylvl5
                            , JBO2.hierarchylvl6
                            , JBO2.hierarchylvl7
                            , JBO2.hierarchylvl8
                            , JBO2.hierarchylvl9
                        FROM (
                            SELECT JBO1.posid
                                , JbO1.actnm
                                , JBO1.prntposid
                                , JBO1.lngcd
                                , JBO1.H0
                                , JBO1.h1
                                , organization.name AS H2
                                , JBO1.Hierarchylvl3
                                , JBO1.hierarchylvl4
                                , JBO1.hierarchylvl5
                                , JBO1.hierarchylvl6
                                , JBO1.hierarchylvl7
                                , JBO1.hierarchylvl8
                                , JBO1.hierarchylvl9
                            FROM (
                                SELECT JBO0.posid
                                    , JbO0.actnm
                                    , JBO0.prntposid
                                    , JBO0.lngcd
                                    , JBO0.H0
                                    , organization.name AS h1
                                    , JBO0.hierarchylvl2
                                    , JBO0.Hierarchylvl3
                                    , JBO0.hierarchylvl4
                                    , JBO0.hierarchylvl5
                                    , JBO0.hierarchylvl6
                                    , JBO0.hierarchylvl7
                                    , JBO0.hierarchylvl8
                                    , JBO0.hierarchylvl9
                                FROM (
                                    SELECT posid
                                        , actnm
                                        , prntposid
                                        , lngcd
                                        , Organization.Name AS H0
                                        , Hierarchylvl1
                                        , hierarchylvl2
                                        , hierarchylvl3
                                        , hierarchylvl4
                                        , hierarchylvl5
                                        , hierarchylvl6
                                        , hierarchylvl7
                                        , hierarchylvl8
                                        , hierarchylvl9
                                    FROM Jobposition
                                    JOIN Organization ON Jobposition.Hierarchylvl0 = organization.OrganizationPk
                                    ) AS JBO0
                                JOIN Organization ON JBO0.hierachylvl1 = organization.organizationpk
                                ) AS JBO1
                            JOIN organization ON Jbo1.hierarchylvl2 = organization.organizationpk
                            ) AS JBO2
                        JOIN Organization ON JBO2.hierarchylvl3 = organization.organizationpk
                        ) AS JBO3
                    JOIN Organization ON JBO3.hierarchylvl4 = organization.organizationpk
                    ) AS JBO4
                JOIN Organization ON JBO4.hierarchylvl5 = organization.organizationpk
                ) AS JBO5
            JOIN Organization ON JBO5.hierarchylvl6 = organization.organizationpk
            ) AS JBO6
        JOIN Organization ON JBO6.hierarchylvl7 = organization.organizationpk
        ) AS JBO7
    JOIN Organization ON JBO7.hierarchylvl8 = organization.organizationpk
    ) AS JBO8
JOIN Organization ON JBO8.hierarchylvl9 = organization.organizationpk

I am not sure if What I am trying to do is allowed in SQL

Upvotes: 0

Views: 56

Answers (1)

George Dando
George Dando

Reputation: 444

It looks like you're trying to do the below:

SELECT posid
, actnm
, prntposid
, lngcd
, o0.Name AS H0
, o1.Name AS H1
, o2.Name AS H2
, o3.Name AS H3
, o4.Name AS H4
, o5.Name AS H5
, o6.Name AS H6
, o7.Name AS H7
, o8.Name AS H8
, o9.Name AS H9
FROM Jobposition jp
JOIN Organization o0 ON jp.Hierarchylvl0 = o0.OrganizationPk
JOIN Organization o1 ON jp.Hierarchylvl1 = o1.OrganizationPk
JOIN Organization o2 ON jp.Hierarchylvl2 = o2.OrganizationPk
JOIN Organization o3 ON jp.Hierarchylvl3 = o3.OrganizationPk
JOIN Organization o4 ON jp.Hierarchylvl4 = o4.OrganizationPk
JOIN Organization o5 ON jp.Hierarchylvl5 = o5.OrganizationPk
JOIN Organization o6 ON jp.Hierarchylvl6 = o6.OrganizationPk
JOIN Organization o7 ON jp.Hierarchylvl7 = o7.OrganizationPk
JOIN Organization o8 ON jp.Hierarchylvl8 = o8.OrganizationPk
JOIN Organization o9 ON jp.Hierarchylvl9 = o9.OrganizationPk

Note that if any of the hierarchy fields are nullable, you'll need to change out all those joins to be LEFT JOIN.

Upvotes: 4

Related Questions