Link to home
Start Free TrialLog in
Avatar of joomla
joomlaFlag for Australia

asked on

Group / Union mysql report

I'm trying to get data out of a huge mysql table

Leader |   Email               |   Name
------------------------------------------
n          |  tt@bbb.com     |  Orla
y          |  a@bbb.com     |   Tom
n          |   p@xyz.com    |   Lary
n          |  xx@bbb.com   |  Richard
n          |  yy@bbb.com   | Iris
y          | ee@xyz.com    | valery


I want to output the list in the following format

Tom     |                |  a@bbb.com
            |  Orla       |  tt@bbb.com
            |  Richard  | xx@bbb.com
            | Iris          | yy@bbb.com
Valery |                | ee@xyz.com
            | Larry      | p@xyz.com


Tom is leader of group who have email addresses @bbb.com
Valery is leader of group  with email address @xzy.com

There are over 40,000 records

Avatar of sshah254
sshah254
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

Ss
Avatar of joomla
joomla
Flag of Australia image

ASKER

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
M
Avatar of joomla
joomla
Flag of Australia image

ASKER

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       |  tt@bbb.com
            |  Richard  | xx@bbb.com
            | Iris          | yy@bbb.com
Tom     |                |  a@bbb.com
            | Larry      | p@xyz.com
Valery |                | ee@xyz.com
ASKER CERTIFIED SOLUTION
Avatar of vbNewbie2009
vbNewbie2009
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of joomla
joomla
Flag of Australia image

ASKER

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