Stack Overflow
Stack Overflow

Questions tagged [ifnull]

This tag refers to the process of determining whether or not a particular object is null, and then running certain code based off of that condition.

Use this tag for questions related to running certain code depending on whether or not one or more objects is null.

161 questions
1
vote
2 answers

mysql syntax for IFNULL with subquery

I want to use subquery inside of IFNULL statement SELECT t.col1 , IFNULL(t.col2, (SELECT an.col_11 FROM another_table an WHERE an.col1 = t.col5)) as alias_name , t.col3 FROM table t; In IFNULL…
Mugdha
  • 851
  • 2
  • 9
  • 12
1
vote
1 answer

How to check null condition in mysql before execute particular set of queries?

I am trying to execute below script in different 5 databases. SET @CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'StudentMaster' AND TABLE_NAME = 'StudentSubjectMa' AND…
Vishal Zanzrukia
  • 4,902
  • 4
  • 38
  • 82
1
vote
1 answer

IfNULL function in yii does not work

In my model I wrote: $criteria->select = ' ( select avg( IfNULL( TR.stars_rating_type_id , 0) ) as stars_rating_type_id from '.$tablePrefix.'tour_review as TR where TR.tour_id = T.id and TR.status = \'A\' ) as reviews_avg_rating '; And I get error…
user2339232
  • 757
  • 3
  • 11
  • 22
1
vote
1 answer

Dynamic value selection on INSERT statement

So my problem is the following. I've got a timeStatus column that will have one of two values on an INSERT statement, 'pending' or 'never', depending on whether the column fromDate is NULL or not. I've made this prepared statement that doesn't work…
Fernando Silva
  • 353
  • 4
  • 20
1
vote
0 answers

SQLite IFNULL() returns undefined

I'm using Javascript for a Phonegap App and SQLite in order to manage my database. This is simple, I'm trying to make this query: SELECT IFNULL(name, 'Not Specified') FROM person When I run it on SQLite Manager it works just fine, returns me "Not…
Agustín
  • 1,546
  • 7
  • 22
  • 41
1
vote
3 answers

Mysql sum of fields not working when either field is NULL

Hi i have this code in php to update a balance field in $table5. Now my problem is that when one of the two fields i.e. add or sub is NULL then this does not return balance. $fetch_balance = mysql_query("SELECT (SUM (IFNULL(`add`,0))-(SUM…
user2649343
  • 147
  • 3
  • 11
1
vote
3 answers

GROUP result value if null

I can't do this query : TABLE: ------------ NOTES | TOTAL ------------ NULL | 23 NULL | 12 pay1 | -13 pay2 | -23 ------------- RESULTS : ------------ NOTES | TOTAL ------------ SELL | 35 pay1 | -13 pay2 |…
sarasa
  • 15
  • 1
  • 5
1
vote
1 answer

mysql select all - check all columns for null with ifnull

I want to return all columns, and anywhere there's a NULL to say 'hey' instead. But this doesn't work SELECT IFNULL(*, 'hey') FROM $table
Lan
  • 1,874
  • 2
  • 20
  • 37
1
vote
5 answers

MySQL IFNULL not returning default value

id category active ------------------------ 1 1 1 2 3 0 3 3 1 4 3 1 5 1 0 I want to select total count of each category and count of active in each cateogry. Since there's…
user1643156
  • 4,407
  • 10
  • 36
  • 59
1
vote
1 answer

How to convert row into column with random with mysql

How to convert row into column with random with mysql? Distinct (image_url) select p.email , image_url AS image_url_1, image_url AS image_url_2 , image_url AS image_url_3, image_url AS image_url_4 FROM …
user1615767
  • 11
  • 5
0
votes
1 answer

MySQL ISNULL with INNER JOIN

I have 2 tables: "shares" and "pending_share_changes". The "shares" table has the following columns: share_ID, asset_ID, member_ID, percent_owner, is_approved pending_share_changes has the following columns: share_change_ID, asset_ID, member_ID,…
Progger
  • 2,266
  • 4
  • 27
  • 51
0
votes
2 answers

how change empty result? mysql

SELECT 'id' FROM `table` WHERE `id` = (select min(`id`) FROM `table` where `id` < 1 and `ids` = 1) id ids 1 1 2 1 5 1 6 1 Result: id = '' I need to change id='' to id='n/a'
user1029691
  • 69
  • 1
  • 4
0
votes
1 answer

Single Mysql query to display multiple columns NULL if those values not present in another table

I have two tables say PROJECT and POST_PROJECT joined by common column PID. PROJECT(PID,HOSTNAME,STATUS) POST_PROJECT(PID,HOSTNAME,POST_STATUS) There are scenarios where Hostname record in PROJECT table does not exist in POST_PROJECT table like…
gjvignesh
  • 5
  • 2
0
votes
2 answers

Query that sets a flag based on a condition for a group of left joined records

I have 2 tables, 'manufacturers' and 'products'. A manufacturer can have multiple products and a product has a 'qty', 'price' and 'cost' field. Manufacturers table manufacturer_id name 1 ABC 2 Meteor 3 XYZ 4 Airfix Products…
Krikey
  • 19
  • 3
0
votes
0 answers

MYSQL, IFNULL or IF functions are not working if used inside update query

UPDATE mytable SET average_play_time = (SELECT ifnull( SEC_TO_TIME(avg(timestampdiff(SECOND, start_time, end_time))), '00:30:00') as average_play_time FROM my_second_table WHERE venue=32 AND start_time > '2018-01-01 14:00:00' ) …
doraemon
  • 325
  • 1
  • 4
  • 16
1 2 3
…
10 11