Wasted 2 hours trying to do something in many lines when that could have been done using just one line of code.

Here’s the problem I wanted to select and display a value either name or fagtcd (code) or staffid whichever is not null from a table.

ID name     fagtcd  staffid
**********************************
1  Andrew   007     -
2  Zairi    -       00188518
3  Shaun    335     101

For example, for the above the selected values should be:-

Andrew 007
Zairi  00188518
Shaun  335      101

Instead of querying 2 times to see each field is null or not it could have been written in one SQL statement:

A colleague once showed me this a year ago but I’ve forgotten so I’m pasting it here on this blog as a reference to myself
$sql = “select fname, coalesce(fagtcd,staffid) from agt_profile

From fname or fagtcd or staffid, only the first field that is not null will be selected. If all the fields are null then no results will be returned.