mysql merging columns

Question

Say i have the following MySql 'orders' table:

id|car|car_qty|speakers|speakers_qty  
1 | 2 |  15   |   5    |   16
2 | 2 |  19   |   5    |   40

What is the query to get the following:

product|qty
  2    | 15
  2    | 19
  5    | 16
  5    | 40

WITHOUT using UNION?

Thank you!

That does not make anything clear. You should explain. Which columns do you want to merge? — Shakti Singh
– Shakti Singh, Commented Apr 13, 2011 at 11:38
Shakti - car and speakers are id's of products, and _qty are the quantity for each of them. i wish to see a detailing of products and their qty. sorry if i wasn't clear. — Israel
– Israel, Commented Apr 13, 2011 at 11:44
it seems to me thay your tabledef in not appropriate. Why did you create speakers and speakers_qty instead of using a column (0 or 1) telling you if a row was a car or a speaker? In that way you could have appended items (and select them) without any trouble — Marco
– Marco, Commented Apr 13, 2011 at 11:50
You are absolutely right, my table planning was wrong and I'm going to fix that by adding an orders_row table. thanks :) — Israel
– Israel, Commented Apr 13, 2011 at 11:59

Marco · Accepted Answer · 2011-04-13 11:46:12Z

2

Try this:

SELECT car product,car_qty qty FROM orders
UNION 
SELECT speakers product,speakers_qty qty FROM orders

edited Apr 13, 2011 at 11:46

answered Apr 13, 2011 at 11:40

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Israel Over a year ago

Sorry I've forgot to mention that I'm looking for a query WITHOUT UNION. fixed the question. Thank you!

Marco Over a year ago

@Israel: why can't you use UNION? Is there a particular reason?

Israel Over a year ago

Yes, the reason is that at the end it will be a much more complicated and dynamic query that will be generated using PHP, and it looks to me that it will be code-wiser to use this approach as the basis instead of multiple UNIONs. I might be wrong :)

PHP Over a year ago

@Israel.. It might be more complicated if you don't use classic feature... Union suits best according to your need.otherwise rethink on table structure.

DRapp · Accepted Answer · 2011-04-13 11:40:07Z

1

Do a union ALL from the same table... Just need to make sure the column names and data types are the same...

select 
      car as product,
      car_qty as qty
  from
      orders
union all
select
      speakers as product,
      speakers_qty as qty
   from
      orders;

answered Apr 13, 2011 at 11:40

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

1 Comment

Israel Over a year ago

Sorry I've forgot to mention that I'm looking for a query WITHOUT UNION. fixed the question. Thank you!

Collectives™ on Stack Overflow

mysql merging columns

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related