• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Mysql Group By Help

Hello,

I have a couple different tables in mysql dealing with permissions.

I have three tables that I'm joining together to try and get the top navigation links, then right underneath, the secondary links to display.  

Here is the SQL that is generated, and a copy of the data and tables are attached as an image.

SELECT programid_tn, link_tn, groupid, topnav_text, hidden_tn, general_access, programid_sl, parentid_sl, link_sl, text_sl, hidden_sl FROM top_navigation LEFT JOIN program_permissions ON top_navigation.programid_tn = program_permissions.programid LEFT JOIN secondarylinks ON top_navigation.programid_tn = secondarylinks.parentid_sl WHERE program_permissions.groupid = '901' or general_access = '1' GROUP BY programid_sl, parentid_sl, programid_tn

It's working decently, so far, but the result set is showing up as the following:

1000 - Home
-

1002 - Programs
-

1003 - Reports
-

1005 - Documents
-

1004 - Forms
- Expense Reports
1004 - Forms
- Out Of Office Request
1004 - Forms
- Petty Cash

...and so forth.

I was hoping that I could group and order this so that it'd show up as

1000 - Home
1002 - Programs
1003 - Reports
1004 - Forms
        - Expense Reports
        - Out Of Office
        - Petty Cash
1005 - Documents


Thank you in advance


screenshot.png
0
t3chguy
Asked:
t3chguy
1 Solution
 
Aaron TomoskyTechnology ConsultantCommented:
Try rearranging your group by, maybe with programid_tn at the front.
You can also just add an order by programid_tn, parentid_sl at the end
0
 
t3chguyAuthor Commented:
Unfortunately, changing that group by clause didn't work as planned.
0
 
SharathData EngineerCommented:
Can you post the result of your query and the expected result in an excel sheet?
0
 
johanntagleCommented:
Unfortunately, I don't know of any MySQL equivalent to the SQL*Plus formatting options in Oracle.  That's usually the tool used to suppress printing when the next row has the same value for the same column.  You may need to handle this via an external script or a stored procedure that will check for the previous and current value of programid_tn.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now