CODEWITHSUNDEEP
mysql
Sumant
Sumant

Reputation: 964

How to extract field name from table having values as 1?

My Table is like 

CREATE TABLE IF NOT EXISTS `pricerange` (
  `priceRangeID` int(11) NOT NULL AUTO_INCREMENT,
  `catID` int(11) NOT NULL,
  `Below 500` tinyint(1) NOT NULL DEFAULT '0',
  `501-1000` tinyint(1) NOT NULL DEFAULT '0',
  `1001-2000` tinyint(1) NOT NULL DEFAULT '0',
  `2001-3000` tinyint(1) NOT NULL DEFAULT '0',
  `3001-4000` tinyint(1) NOT NULL DEFAULT '0',
  `4001-5000` tinyint(1) NOT NULL DEFAULT '0',
  `5001-6000` tinyint(1) NOT NULL DEFAULT '0',
  `6001-7000` tinyint(1) NOT NULL DEFAULT '0',
  `7001-8000` tinyint(1) NOT NULL DEFAULT '0',
  `8001-9000` tinyint(1) NOT NULL DEFAULT '0',
  `9001-10000` tinyint(1) NOT NULL DEFAULT '0',
  `10001-100000` tinyint(1) NOT NULL DEFAULT '0',
  `above 100000` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`priceRangeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `pricerange` (`priceRangeID`, `catID`, `Below 500`, `501-1000`, `1001-2000`, `2001-3000`, `3001-4000`, `4001-5000`, `5001-6000`, `6001-7000`, `7001-8000`, `8001-9000`, `9001-10000`, `10001-100000`, `above 100000`) VALUES
(1, 3, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 1, 1, 1);

Here i want to extract the field name having values 1 for a given catID. Can any one help me how to write this query in mysql?

I have tried SHOW FIELDS from pricerange but it shows all field names with its details its not dealing with values, SELECT * from pricerange but it needs some php manipulations. But i want to write it in MYSQL only.

Upvotes: 1

Views: 508

Answers (4)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

The key to solving this problem is to use the information_schema

Here is the query for you:

SELECT A.column_name FROM
(select ordinal_position-3 pos,column_name from information_schema.columns
where table_name='pricerange' and LOCATE('-',column_name)) A
INNER JOIN
(SELECT CONCAT(`501-1000`,`1001-2000`,`2001-3000`,
`3001-4000`,`4001-5000`,`5001-6000`,`6001-7000`,
`7001-8000`,`8001-9000`,`9001-10000`,
`10001-100000`) bitmap FROM pricerange) B
ON SUBSTR(bitmap,pos,1) = '1';

I loaded your data

mysql> DROP DATABASE IF EXISTS sumant;
Query OK, 1 row affected (0.06 sec)
mysql> CREATE DATABASE sumant;
Query OK, 1 row affected (0.00 sec)
mysql> USE sumant
Database changed
mysql> CREATE TABLE IF NOT EXISTS `pricerange` (
    ->   `priceRangeID` int(11) NOT NULL AUTO_INCREMENT,
    ->   `catID` int(11) NOT NULL,
    ->   `Below 500` tinyint(1) NOT NULL DEFAULT '0',
    ->   `501-1000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `1001-2000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `2001-3000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `3001-4000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `4001-5000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `5001-6000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `6001-7000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `7001-8000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `8001-9000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `9001-10000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `10001-100000` tinyint(1) NOT NULL DEFAULT '0',
    ->   `above 100000` tinyint(1) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`priceRangeID`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO `pricerange` (`priceRangeID`, `catID`, `Below 500`, `501-1000`, `1001-2000`, `2001-3000`, `3001-4000`, `4001-5000`, `5001-6000`, `6001-7000`, `7001-8000`, `8001-9000`, `9001-10000`, `10001-100000`, `above 100000`) VALUES
    -> (1, 3, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 1, 1, 1);
Query OK, 1 row affected (0.07 sec)

mysql>

Here is the query executed:

mysql> SELECT A.column_name FROM
    -> (select ordinal_position-3 pos,column_name from information_schema.columns
    -> where table_name='pricerange' and LOCATE('-',column_name)) A
    -> INNER JOIN
    -> (SELECT CONCAT(`501-1000`,`1001-2000`,`2001-3000`,
    -> `3001-4000`,`4001-5000`,`5001-6000`,`6001-7000`,
    -> `7001-8000`,`8001-9000`,`9001-10000`,
    -> `10001-100000`) bitmap FROM pricerange) B
    -> ON SUBSTR(bitmap,pos,1) = '1';
+--------------+
| column_name  |
+--------------+
| 5001-6000    |
| 6001-7000    |
| 9001-10000   |
| 10001-100000 |
+--------------+
4 rows in set (0.02 sec)

mysql>

It also works in reverse. Here is the same query but hunting down only zeros:

mysql> SELECT A.column_name FROM
    -> (select ordinal_position-3 pos,column_name from information_schema.columns
    -> where table_name='pricerange' and LOCATE('-',column_name)) A
    -> INNER JOIN
    -> (SELECT CONCAT(`501-1000`,`1001-2000`,`2001-3000`,
    -> `3001-4000`,`4001-5000`,`5001-6000`,`6001-7000`,
    -> `7001-8000`,`8001-9000`,`9001-10000`,
    -> `10001-100000`) bitmap FROM pricerange) B
    -> ON SUBSTR(bitmap,pos,1) = '0';
+-------------+
| column_name |
+-------------+
| 501-1000    |
| 1001-2000   |
| 2001-3000   |
| 3001-4000   |
| 4001-5000   |
| 7001-8000   |
| 8001-9000   |
+-------------+
7 rows in set (0.01 sec)

mysql>

Give it a Try !!!

Upvotes: 1

kcsoft
kcsoft

Reputation: 2947

You could use something like this:

SELECT
GROUP_CONCAT( (IF `Below 500`=1, `Below 500`, ''), (IF `501-1000`=1, `501-1000`, ''),...)
FROM `pricerange`
WHERE `catID`=myCatID

But I would change that DB schema if possible. Have a separate table created and a record for each price interval.

Upvotes: 0

Jer In Chicago
Jer In Chicago

Reputation: 828

Maybe you want to use the case statement... for example

select case "Below 500" when 1 then "Below 500" else "" end, 
       case "501-1000" when 1 then "501-1000" else "" end, etc...

Upvotes: 0

a1ex07
a1ex07

Reputation: 37354

I don't think SQL is the right tool for that task (given your table structure), it's much easier to read the whole record and get names on client side. But it's possible with sql as well. Idea is to unpivot :

SELECT 'Below 500' as field1
FROM  pricerange where catID = 1 AND `Below 500` is not null
UNION ALL
SELECT '501-1000' as field1
FROM  pricerange where catID = 1 AND `501-1000` is not null

etc.

Upvotes: 0

Related Questions