asked on

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

ASKER CERTIFIED SOLUTION

Aaron Tomosky

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

t3chguy

ASKER

Unfortunately, changing that group by clause didn't work as planned.

Sharath S

Can you post the result of your query and the expected result in an excel sheet?

johanntagle

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.