I have a table which has a single entry. I have to get those column values whose values are not null. Please suggest me query for MySQL so I can implement this. My table is :
In this table 3 columns have Null values. So I don't want these columns, query should return values which in not null.
Can I get the column name also? Like I want to get name of the column i.e min_p5 whose value is not null. So I can break the column name into strings and use 5 in my calculation. Please suggest me answer.
I think this is what you need:
Assuming your table name to be "orders" [pls change it accordingly]
$q="show columns from orders";
$res=mysql_query($q) or die(mysql_error());
$arr_field=array();
while($row=mysql_fetch_object($res)){
$field=$row->Field;
$q1="select ".$field." from orders where ".$field."!=0"; //if string then '0'
$res1=mysql_query($q1) or die(mysql_error());
if(mysql_num_rows($res1)>0){
$arr_field[]=$field;
}
}
$q="select ";
foreach($arr_field as $field){
$q.=$field.",";
}
$q=rtrim($q,",");
$q.=" from orders";
$res=mysql_query($q) or die(mysql_error());
while($row=mysql_fetch_object($res)){
foreach($arr_field as $field){
print($field."==".$row->$field."<br/>");
}
}
Run this and I hope you will get an idea...
SELECT *
FROM table
WHERE YourColumn IS NOT NULL;
select * from table where column_name is not null
Try following query for solve your problem,
select * from table
where column_name IS NOT NULL
It seems like you want to return:
| POINT_ID | BUS_ID | MIN_P5 | MIN_P15 |
|----------|--------|--------|---------|
| P101 | B101 | 1000 | 3000 |
because you want to exclude columns that have a zero value. It is not too easy to do this in MySQL because you need to use prepared statements:
SELECT
CONCAT(
'SELECT CONCAT(\'SELECT \',
CONCAT_WS(\',\',',
GROUP_CONCAT(
CONCAT(
'CASE WHEN EXISTS(SELECT * FROM TABLENAME WHERE ',
`column_name`,
'!=\'0\') THEN \'',
`column_name`,
'\' END')
),
'),\' FROM tablename\') FROM tablename INTO @final_sql'
)
FROM `information_schema`.`columns`
WHERE `table_schema`=DATABASE()
AND `table_name`='tablename'
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
PREPARE finalstmt FROM @final_sql;
EXECUTE finalstmt;
Please see fiddle here. If your columns are numbers and not strings, maybe some minor fixes are needed. However, I would suggest you to try a different approach or to rethink about your table structure.
SELECTCASEin case check column value isNOT NULLthen return column name otherwise return NULL