Guest marvink Posted December 15, 2010 Share Posted December 15, 2010 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted December 16, 2010 Share Posted December 16, 2010 You'll probably need to remove the join on a1 and only join staff to the temporary a2 table. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.