CODEWITHSUNDEEP
mysqlsql
user321605
user321605

Reputation: 836

MYSQL : writing a query where a column is the result of a boolean expression

I have a table where i have columns such as:

deposit_received
paperwork_received
preferred_physician
preferred_physician_phone

One of the views that we use this data involves displaying whether there is any of this data missing. I can arrange this trivially in PHP, but I'd rather do it in MySQL (it leverages a framework's sort by feature if I can do it in MySQL.

So essentially, I need to write a query that returns: 

col1 | col2 | ... | (deposit_received == true && paperwork_received == true && preferred_physician != null && preferred_physician_phone != null)

I've looked at some of the functions you can use, but I can't wrap my head around it.

Thanks in advance! Rob

Upvotes: 2

Views: 108

Answers (2)

macarthy
macarthy

Reputation: 3069

Look at the COALESCE function to deal with the NULLs e.g. The function 'COALESCE' can simplify working with null values. For example, to treat null as zero, you can use:

select COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

Upvotes: 0

The Scrum Meister
The Scrum Meister

Reputation: 30111

You can always just write the query:

SELECT col1, col2,
    (deposit_received
    AND paperwork_received 
    AND preferred_physician IS NOT NULL
    AND preferred_physician_phone IS NOT NULL)
  AS Result
FROM tableName

The parentheses are optional, however it improves readability.

Upvotes: 3

Related Questions