We help IT Professionals succeed at work.

Mysql Group By Help

t3chguy
t3chguy asked
on
271 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Director, SD-WAN Solutions
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Unfortunately, changing that group by clause didn't work as planned.
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
Can you post the result of your query and the expected result in an excel sheet?
Top Expert 2012

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.