Group / Union mysql report

Posted on 2011-05-04
Last Modified: 2012-05-11
I'm trying to get data out of a huge mysql table

Leader |   Email               |   Name
n          |     |  Orla
y          |     |   Tom
n          |    |   Lary
n          |   |  Richard
n          |   | Iris
y          |    | valery

I want to output the list in the following format

Tom     |                |
            |  Orla       |
            |  Richard  |
            | Iris          |
Valery |                |
            | Larry      |

Tom is leader of group who have email addresses
Valery is leader of group  with email address

There are over 40,000 records

Question by:joomla
    LVL 9

    Expert Comment

    Select * from (
    select name as Name1, '' as Name2, Email from mytable where leader = 'Y' Union
    select '' as Name1, name as Name2, Email from mytable where leader = 'N'
    ) x
    order by Email, Name1, Name2


    Author Comment

    Hi Ss
    thanks for your input
    unfortunately it didn't work
    obviously it produced an output, but it's when I tried on a subset of 17 records it returns 34 records

    i can't find an obvious link to help you intrepret the results

    Author Comment

    Hi Ss,
    my apolgies, there were actually 34 records in the table I was working on

    The output is not quite what I want   (see below)
    I tried changing the order in the syntax but it makes do diff

                |  Orla       |
                |  Richard  |
                | Iris          |
    Tom     |                |
                | Larry      |
    Valery |                |
    LVL 1

    Accepted Solution

    You really need a fourth column or a substring of the email column to just pull out the domain name.  When ordering by the entire email address it simply does an alpha-sort; not alpha-sort by domain name.

    Then when ordering by 'Email', 'Name2', 'Name1' it will appear as desired.

    I through together a different query option that would use a case instead of Union.  They would both get you to the same end result but using the case only necessitates parsing the table recordset once as opposed to multiple times with the union
    select *
    	case leader
    	     when 'y' then name
    	end as Leader
    	,case leader
    	     when 'n' then name
    	end as Follower
    	,SUBSTRING(email,INSTR(email,'@') + 1) as domain
         from tblName
    order by domain, follower, leader

    Open in new window


    Author Closing Comment

    thanks for offering this solution.
    my apologies for not getting back sooner.
    i had been on a deadline and lost focus

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Creating and Managing Databases with phpMyAdmin in cPanel.
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now