I have a quite unique need to make select always return one row
select * from table1 Where (table1.pk = :p1) or (table1.fk1 = :p1)
The above SQL always has two cases for return:
1- my Select return two records: The only different is one of the records has data while the other has only the ID filled with data while the rest of its fields are null. I need in this case to return <strong>only</strong> the one that has data in other fields.
2- my Select return one record In this case the record returned has only the ID field filled with data while the rest of the fields are null however this is what I want and no need for any further processing.
Please advise if is it possible to do that in one plain Select SQL. I can not use stored procedure.
You can use the
first clause of the
select statement to get only 1 row.
Given your specific conditions, you can order the result set descending by the rest of the fields to be sure the null row is selected only in case there's no data row (null goes first in firebird 2.5, but AFAIK this changed somewhere in the last versions, so check your specific version before applying this).
Your final query will look like this:
select first 1 * from table1 where (table1.pk = :p1) or (table1.fk1 = :p1) order by somecolumn;
somecolumn being the most relevant of the other fields that can contain null values.
you can test this with this statements:
--two rows, one with ID and values and the other with ID and null with q1 as ( select 1 id, 'data' othercolumn from rdb$database union select 2 id, null othercolumn from rdb$database ) select first 1 * from q1 order by othercolumn nulls last; --versus: --onw row with ID and null with q1 as ( select 2 id, null othercolumn from rdb$database ) select first 1 * from q1 order by othercolumn nulls last;