cascading join on same table, confused

[coderb]

hi all, I'm stumped, please help.

 

I've got a salesperson table with 4 columns:

id

name

role  (1 = national manager, 2 = regional manager, 3 = local manager, 4 = salesperson)

manager_id

 

I need to create a joined result set that displays national manager in col 1, and all regional managers within that, in col 2, and all local managers within that, in col 3 and finally all salespeople within that, in col 4.

 

 

here is my attempt, but this returns 20 times the original number of table rows and combines all rows in all columns.

I've included all table cols here, but need only name cols:

 

SELECT tblManagersnat.sp_id, tblManagersnat.sp_name, tblManagersnat.role,

          tblManagersreg.sp_id, tblManagersreg.sp_name, tblManagersreg.role, tblManagersreg.manager_id,

          tblManagersloc.sp_id, tblManagersloc.sp_name, tblManagersloc.role, tblManagersloc.manager_id,

          tblSalesPeople.sp_id, tblSalesPeople.sp_name, tblSalesPeople.role, tblSalesPeople.manager_id

FROM tblSalesPeople AS tblManagersnat

LEFT JOIN tblSalesPeople AS tblManagersreg ON tblManagersreg.manager_id = tblManagersnat.sp_id

LEFT JOIN tblSalesPeople AS tblManagersloc ON tblManagersloc.manager_id = tblManagersreg.sp_id

LEFT JOIN tblSalesPeople ON tblSalesPeople.manager_id = tblManagersloc.sp_id

ORDER BY tblManagersnat.sp_name, tblManagersreg.sp_name, tblManagersloc.sp_name, tblSalesPeople.sp_name;

 

So, in col 1 I only want national managers listed (role =1), in col 2 I on want regional managers listed ... and so on..

 

Appreciate any help thanks...

 

 

 

 

 

[fenway]

You want all managers for each roleID in separate columns?

[coderb]

Yeah, national managers name must always be in col1, regional in col2, local in col3 and salespeople in col4

I tried a couple more options and got this to return the correct number of rows and linked to the correct managers, however the columns are not quite correct.

ie - if a salesperson is directly linked to a national manager, then the national managers name is listed in the 3rd column instead of the first column with null values in the second and third.  Any ideas? thanks.

 

here's my sql:

SELECT

tblManagersnat.sp_id, tblManagersnat.sp_name, tblManagersnat.role,

tblManagersreg.sp_id, tblManagersreg.sp_name, tblManagersreg.role, tblManagersreg.manager_id,

tblManagersloc.sp_id, tblManagersloc.sp_name, tblManagersloc.role, tblManagersloc.manager_id,

tblSalesPeople.sp_id, tblSalesPeople.sp_name, tblSalesPeople.role, tblSalesPeople.manager_id

FROM tblSalesPeople

LEFT JOIN tblSalesPeople AS tblManagersloc ON tblSalesPeople.manager_id = tblManagersloc.sp_id

LEFT JOIN tblSalesPeople AS tblManagersreg ON tblManagersloc.manager_id = tblManagersreg.sp_id

LEFT JOIN tblSalesPeople AS tblManagersnat ON tblManagersreg.manager_id = tblManagersnat.sp_id

ORDER BY tblManagersnat.sp_name, tblManagersreg.sp_name, tblManagersloc.sp_name, tblSalesPeople.sp_name

 

 

[fenway]

Can we get some sample output (i.e. current and what you want it to show)?

[coderb]

sure, below examples:

 

these salespeople are directly linked to a national manager (role = 1) but I need the national manager details to be in the first column set, but is displayed in local manager position (3rd columnset)

 

current:

sp_id sp_name role sp_id sp_name role manager_id sp_id sp_name role manager_id sp_id sp_name role manager_id

NULL NULL NULL NULL NULL NULL NULL 3 Tony Guerrieri 1 0 132 J. Walker Stevens 4 3

NULL NULL NULL NULL NULL NULL NULL 3 Tony Guerrieri 1 0 90 Jerry Boughton 4 3

NULL NULL NULL NULL NULL NULL NULL 3 Tony Guerrieri 1 0 109 Jina McAvey 4 3

NULL NULL NULL NULL NULL NULL NULL 3 Tony Guerrieri 1 0 128 Joan Campbell 4 3

 

what I need:

sp_id sp_name role sp_id sp_name role manager_id sp_id sp_name role manager_id sp_id sp_name role manager_id

3 Tony Guerrieri 1 0 NULL NULL NULL NULL NULL NULL NULL 132 J. Walker Stevens 4 3

3 Tony Guerrieri 1 0 NULL NULL NULL NULL NULL NULL NULL 90 Jerry Boughton 4 3

3 Tony Guerrieri 1 0 NULL NULL NULL NULL NULL NULL NULL 109 Jina McAvey 4 3

3 Tony Guerrieri 1 0 NULL NULL NULL NULL NULL NULL NULL 128 Joan Campbell 4 3

 

so I need the names to always be in the correct columns, not shifted right as current.

thanks

 

[coderb]

would I maybe need to use a 'group by' clause? somehow....

[fenway]

I can't make sense of that outpu.