Select values from the same column

Question

I have a database related question for WordPress.

My table is represented as follows:

ID | customID | column1 | column2

1      8      _item    item number

2      8      _price    item price

3      9      _item    item number

4      9      _price    item price

I want to retrieve the following rows using a MySQL SELECT statement:

CustomID | _price | _item |

8       item price(from col2)    item number(from col2)
9       item price(from col2)    item number(from col2)

Is this possible? The value _price and _item should be shown as columns with values from column2. How to solve this?

How do you know when its an item number or an item price? Does the data literally say item number/item price? Or is the data like Item Number: Z123 and Item Price: $3.35? — lnjblue
– lnjblue, Commented May 12, 2017 at 17:33
ye item number and item price are values for each product. _price and _item are literally _price and _item. I'll try @LSA solution as soon as possible — Sadbot
– Sadbot, Commented May 13, 2017 at 20:14

Sebastian Lerch · Accepted Answer · 2017-05-12 17:55:07Z

1

Your tables are designed quite badly. You should build an extra table which maps product and price. However this is what you can do with your solution:

Select t1.customID, t1.column2, t2.column2 from <tablename> t1,
 <tablename> t2 
 where t1.customID = t2.customId 
 and t1.column1 like '_item' 
 and t2.column1 like '_price';

answered May 12, 2017 at 17:55

Sebastian Lerch

4247 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

Select values from the same column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related