MySql return multiple rows from different columns of the same row

Question

Given two tables:

The 'people' table contains the following columns:

name
favorite_walking_shoe
favorite_running_shoe
favorite_dress_shoe
favorite_house_shoe
favorite_other_shoe

The 'shoes' table contains the following columns:

shoe
name
description

I want to create a result set that contains:

people.name, people.favorite_shoe_type, shoes.name, shoes.description

I know I can get the desired results using something like:

select p.name, p.favorite_shoe_type, s.name, s.description
  from (select name, favorite_walking_shoe as shoe, 'walking' as favorite_shoe_type
          from people where favorite_walking_shoe is not null
        union all
        select name, favorite_running_shoe, 'running'
          from people where favorite_running_shoe is not null
        union all
        select name, favorite_dress_shoe, 'dress'
          from people where favorite_dress_shoe not is null
        union all
        select name, favorite_house_shoe, 'house'
          from people where favorite_house_shoe not is null
        union all
        select name, favorite_other_shoe, 'other'
          from people where favorite_other_shoe not is null
        ) p
     join shoes s on s.shoe = p.shoe
    order by 1,2

but this would require 5 passes of the 'people' table. Is there a way to accomplish the UNION ALLs without requiring multiple passes?

I should point out that the structures are part of a vendor product which I cannot modify. :(

that's more of a pivot query, which mysql doesn't support. you'd be better off just pulling a normal select user, shoe_show query and then doing the transformation in client-side code. — Marc B
– Marc B, Commented Feb 27, 2013 at 17:46
My suggestion would be to start with the shoes table and join them with each favorite shoe type on the people table. You hopefully have fewer shoes than people. — Narnian
– Narnian, Commented Feb 27, 2013 at 17:55

Gordon Linoff · Accepted Answer · 2013-02-27 18:43:35Z

You can get around the five scans by doing a cross join:

select p.name, p.favorite_shoe_type, s.name, s.description
from (select p.*,
             (case when favorite_shoetype = 'walking' then p.favore_walking_shoe
                   when favorite_shoetype = 'running' then p.favorite_running_shoe
                   when favorite_shoetype = 'dress' then p.favorite_dress_shoe
                   when favorite_shoetype = 'house' then p.favorite_house_shoe
                   when favorite_shoetype = 'other' then p.favorite_other_shoe
              end) as shoe
      from people p cross join
           (select 'walking' as favorite_shoe_type union all
            select 'running' union all
            select 'dress' union all
            select 'house' union all
            select 'other'
           ) shoetypes join
           shoes s
     ) p
     on s.shoe = p.shoe

I'm not sure this will be more efficient. If you have indexes on shoe, this even more complicated version might be more efficient:

select p.name, p.favorite_shoe_type, s.name, s.description
from (select p.name, favorite_shoe_types,
             (case when favorite_shoetype = 'walking' then ws.name
                   when favorite_shoetype = 'running' then rs.name
                   when favorite_shoetype = 'dress' then ds.name
                   when favorite_shoetype = 'house' then hs.name
                   when favorite_shoetype = 'other' then os.name
              end) as name,
             (case when favorite_shoetype = 'walking' then ws.description
                   when favorite_shoetype = 'running' then rs.description
                   when favorite_shoetype = 'dress' then ds.description
                   when favorite_shoetype = 'house' then hs.description
                   when favorite_shoetype = 'other' then os.name
              end) as description
      from people p left outer join
           shoes ws
           on ws.shoe = favorite_walking_shoe left outer join
           shoes rs
           on rs.shoe = favorite_running_shoe left outer join
           shoes ds
           on ds.shoe = favorite_dress_shoe left outer join
           shoes hs
           on hs.shoe = favorite_house_shoe left outer join
           shoes os
           on os.shoe = favorite_other_shoe cross join
           (select 'walking' as favorite_shoe_type union all
            select 'running' union all
            select 'dress' union all
            select 'house' union all
            select 'other'
           ) shoetypes 
     ) p
     on s.shoe = p.shoe
where s.name is not null

This should do the five joins using indexes -- quite fast, one scan of the people table, and feed this to the cross join. The logic then returns the values that you want.

Note: both of these are untested so they might have syntax errors.

Thanks Gordon. CROSS JOIN is the solution I was looking for.

Taryn · Accepted Answer · 2013-02-27 19:07:31Z

0

Unfortunately, the way your current table is structured you will have multiple passes to get each value. If it is possible, I would suggest changing your table structure to the include a shoe_type table and then a join table between the people and shoes and in this table, you can include a flag that will show if the shoe is the favorite.

So it will be similar to this:

create table people_shoe
(
    people_id int,
    shoe_id int,
    IsFavorite int
);

You could also have a shoe_type table to store each of the different show types:

create table shoe_type
(
    id int,
    name varchar(10)
);

insert into shoe_type
values('Walking'), ('Running'), ('Dress'), ('House'), ('Other');

The shoe_type.id would be added to your shoe table and you would join the tables.

Edit #1, if you can remodel the database, you could use the following (mock-up model):

create table people
(
    id int, 
    name varchar(10)
);
insert into people values (1, 'Jim'), (2, 'Jane');

create table shoe_type
(
    id int,
    name varchar(10)
);
insert into shoe_type
values(1, 'Walking'), (2, 'Running'), (3, 'Dress'), (4, 'House'), (5, 'Other');

create table shoes
(
    id int,
    name varchar(10),
    description varchar(50),
    shoe_type_id int
);
insert into shoes 
values(1, 'Nike', 'test', 2), (2, 'Cole Haan', 'blah', 3);

create table people_shoe
(
    people_id int,
    shoe_id int,
    IsFavorite int
);
insert into people_shoe
values (1, 1, 1),
(1, 2, 0),
(2, 1, 1);

Then when you query, your code will be similar to this:

select p.name PersonName,
  s.name ShoeName,
  st.name ShoeType,
  ps.isFavorite
from people p
inner join people_shoe ps
    on p.id = ps.people_id
inner join shoes s
    on ps.shoe_id = s.id
inner join shoe_type st
  on s.shoe_type_id = st.id

See SQL Fiddle with Demo

edited Feb 27, 2013 at 19:07

answered Feb 27, 2013 at 18:01

Taryn

249k57 gold badges374 silver badges409 bronze badges

5 Comments

gwc Over a year ago

Yes, creating a third table to resolve the many-to-many relationship would be ideal. Unfortunately, I have no control over the structures. I just have to try to create the most efficient query I can given what I have. Thanks for the answer.

gwc Over a year ago

But, you have prompted me to consider this approach none the less. I could create insert, update, and delete triggers on the people table which could automatically maintain the people_shoe table. I'll ponder that a bit. Thanks again.

Taryn Over a year ago

@gwc I think your best solution would be to normalize that table, it would solve many of your issues. :)

Taryn Over a year ago

@gwc see my edit, I included a sample data model if you can remodel your database.

gwc Over a year ago

I completely agree. Unfortunately, it's not an option. I can create and maintain a new normalized table from the original using triggers. I think that is where I've landed.

Collectives™ on Stack Overflow

MySql return multiple rows from different columns of the same row

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related