Required to retrieve distinct list of names from 2 tables based on ranking and levels

[Guest marvink]

Hi,I have 2 tables - namely staff and approval_structure. The table structure is as follows (I only showed those columns that we will use, the rest are not impt): staffstaff_idname~~approval_structureappstr_idstaff_identity - depicts which entity this approval structure belongs toappr_type - type of approval, i.e. pr/poapprstr_level - divided into 5 levels or groups of people, with different ranks within themapprstr_rank - ranks of people within each groupappr_limit_id - limits based on amount of PO staff is allowed to approve based on DOAI have entered the sample data below in those tables above:

[u]staff[/u][b]staff_id        name[/b]17               John18               Law21               Han40               Jan83               Joan[u]approval_structure[/u][b]appstr_id     staff_id    entity    appr_type      apprstr_level      apprstr_rank      appr_limit_id [/b]1                21            PIL       po                 1                       1                       52                40            PIL       po                 1                       2                       53                18            PIL       po                 2                       1                       54                17            PIL       po                 3                       1                       55                83            PIL       po                 3                       2                       56                17            PIL       po                 4                       1                       57                83            PIL       po                 4                       2                       5

I used a subquery and the SQL statement I wrote was below:

SELECT a1.apprstr_level, a1.apprstr_rank, a1.appr_limit_id, a1.staff_id, s.nameFROM approval_structure a1 INNER JOIN staff s ON s.staff_id=a1.staff_idINNER JOIN (	SELECT apprstr_level, MIN(apprstr_rank) AS arank, entity, appr_type	FROM approval_structure	WHERE entity='PIL'	AND appr_type='po'	AND staff_id<>'3'	GROUP BY apprstr_level, entity, appr_type	) a2ON a1.entity=a2.entity AND a1.appr_type=a2.appr_type AND a1.apprstr_level=a2.apprstr_level AND a1.apprstr_rank=a2.arankINNER JOIN approval_limit al ON al.lvl=a1.appr_limit_idWHERE al.lower_limit<=25005 AND al.upper_limit>= CASEWHEN al.upper_limit=0 THEN 0ELSE 25005ENDORDER BY a1.apprstr_level

The Result appeared as :

[b]apprstr_level         apprstr_rank         appr_limit_id        staff_id          name[/b]1                          1                          5                         21                 Han2                          1                          5                         18                 Law3                          1                          5                         17                 John4                          1                          5                         17                 [b]John[/b]

I don't want repeats of staff to appear in the list, i.e. John. I want the list to appear as:

[b]apprstr_level         apprstr_rank         appr_limit_id        staff_id          name[/b]1                          1                          5                         21                 Han2                          1                          5                         18                 Law3                          1                          5                         17                 John[b]4[/b]                          [b]2[/b]                          [b]5[/b]                         [b]83[/b]                 [b]Joan[/b]

How do I do this? I tried modifying my sql to no avail. Could someone out there help me?Warmest regards,Marvin

[justsomeguy]

You'll probably need to remove the join on a1 and only join staff to the temporary a2 table.