Using a hierarchy table

[younggunnaz69]

I have two tables like below and I'm trying to create a query to combine the two. Examples are below. I tried to search but could not find any help. Thanks for your help.



tbl_hierarchy

Store_Num	RM_ID	GM_ID	M_ID
1	E111	E223	E323
2	E111	E224	E324
3	E112	E225	E325
4	E112	E226	E326

tbl_emp

E_ID	FName
E111	Tom
E112	Bob
E223	Jim
E224	Sam
E226	Sarah
E323	Ann
E324	Marie
E325	Kelly
E326	Connie

Result-

Store_Num	RM_ID	RM_Name	GM_ID	GM_Name	M_ID	M_Name
1	E111	Tom	E223	Jim	E323	Ann
2	E111	Tom	E224	Sam	E324	Marie
3	E112	Bob	E225		E325	Kelly
4	E112	Bob	E226	Sarah	E326	Connie

[recyan]

Just check out if below gives some guidelines :

SELECT 
    tbl_hierarchy.Store_Num, 
    tbl_hierarchy.RM_ID, 
    tbl_emp.E_ID, 
    tbl_emp.FName, 
    tbl_hierarchy.GM_ID, 
    tbl_emp_1.E_ID, 
    tbl_emp_1.FName, 
    tbl_hierarchy.M_ID, 
    tbl_emp_2.E_ID, 
    tbl_emp_2.FName
FROM 
    (
        (
            tbl_hierarchy 
            LEFT JOIN 
            tbl_emp 
            ON 
            tbl_hierarchy.RM_ID = tbl_emp.E_ID
        ) 
        LEFT JOIN 
        tbl_emp AS tbl_emp_1 
        ON 
        tbl_hierarchy.GM_ID = tbl_emp_1.E_ID
    ) 
    LEFT JOIN 
    tbl_emp AS tbl_emp_2 
    ON 
    tbl_hierarchy.M_ID = tbl_emp_2.E_ID;

Thanks

[younggunnaz69]

Thanks it worked fine. I deleted the   in the join statments. I wasnt sure what they were for?? But other than that it worked fine. Thanks again!!

[recyan]

I deleted the   in the join statments. I wasnt sure what they were for??

It is not supposed to be there. Guess some formatting issue.

Thanks it worked fine.

Glad, you found it helpful.

Thanks