Select a Column If Other Column Is Null

Select a column if other column is null

You need the ISNULL function.

SELECT ISNULL(a, b)

b gets selected if a is null.

Also, you can use the WHEN/THEN select option, lookup in BOL. Essentially: its c switch/case block meets SQL.

replace a column with another column when column is null in SELECT statement

Use coalesce():

select coalesce(a, b) as a
from t;

SQL Select Columns.. IF NULL then Select other Columns

Ok, I've wrapped all column names in [square brackets] because you're using reserved names (Key, Text, Type) and I like consistency, it's worth breaking this habit as soon as possible.

If your criteria is that all three columns (H1, H2, H3) need to be NULL then you'll want something like this;

SELECT [ID]
    ,[key]
    ,[Product]
    ,[Item]
    ,[Block]
    ,[Source]
    ,CASE 
        WHEN H1 IS NULL
            AND H2 IS NULL
            AND H3 IS NULL
            THEN [Title]
        ELSE NULL
        END AS [Title]
    ,CASE 
        WHEN H1 IS NULL
            AND H2 IS NULL
            AND H3 IS NULL
            THEN [Text]
        ELSE NULL
        END AS [Text]
    ,CASE 
        WHEN H1 IS NULL
            AND H2 IS NULL
            AND H3 IS NULL
            THEN [Type]
        ELSE NULL
        END AS [Type]
    ,H1
    ,H2
    ,H3
FROM DataTable

SELECT one column if the other is null

The ANSI means is to use COALESCE:

SELECT COALESCE(a2.date, a1.date) AS `date`
   ...

The MySQL native syntax is IFNULL:

SELECT IFNULL(a2.date, a1.date) AS `date`
   ...

Unlike COALESCE, IFNULL is not portable to other databases.

Another ANSI syntax, the CASE expression, is an option:

SELECT CASE
         WHEN a2.date IS NULL THEN a1.date
         ELSE a2.date
       END AS `date`
   ...

It requires more direction to work properly, but is more flexible if requirements change.

SQLITE - If a column is null then the value in another column is 0

If Present is null then, I want to assign 0 to the Score value.

The case expression goes like:

select 
    present,
    case when present is null 
        then 0 
        else score 
    end as score
from ...

You don’t tell what to do when present is not null - so this returns the original score.

It is unclear why you would need distinct. If you were to ask a question about the original query, which seems to produce (partial) duplicates, on might be able to help fix it.

Select another column value from the same table if another column value is NULL

SELECT NAME, AGE, ISNULL(PRIMARYWEIGHT, SECONDARYWEIGH) As WEIGHT 

msdn reference

chose another column if the first column is null

I have two options depending on one question - if email and phone are not null and phone exists in the database, do you want to raise the error or enter the new data?

If you always want to check if the email or phone exists then could you just use OR?

$chk_query = " SELECT coalesce(email,phone) FROM users WHERE email = '$email' OR phone = '$phone' ";
            $result = $mysqli->query($chk_query);
            if ($result->num_rows > 0 ){
                $_SESSION['message'] = 'The Email or Phone number already exists';
            }
            else{
                // insert data to the database after checking

If you want to ignore the phone when email has been entered then add an extra condition (whilst only returning the first non-null column

$chk_query = " SELECT coalesce(email,phone) FROM users WHERE email = '$email' OR (email IS NULL AND phone = '$phone') ";
            $result = $mysqli->query($chk_query);
            if ($result->num_rows > 0 ){
                $_SESSION['message'] = 'The Email or Phone number already exists';
            }
            else{
                // insert data to the database after checking

The coalesce will choose the first non-null value in the list of columns. Note this will only work if values are truly null, not if they are empty strings.

---

Related Topics