MySQL check two columns for value but with a preferred column result

I have a MariaDB SQL table, with two different ID rows.

Id’s have been imported from a previous DB version (old_id reference is from another table), as a transitional measure searches need to be done to find id’s, with a preference for the older id value. Only ONE row can ever be returned .

Table:

new_id(PK)  |  old_id  |   data   |   more_data 
---------------------------------------------
   1        |    34    |   harge  |     spalt
   2        |     7    |   greet  |    sausages
   4        |   852    |  humbug  |    gertrude
   6        |    13    |  bloody  |   festivalz
   7        |   412    |   hello  |   fiddlests
   8        |     3    |  fraggo  |  piddlebiscs

new_id is the Primary Key.

So:

• When the page is loaded with ID=852 it needs to return row 4
• When the page is loaded with ID=7 it needs to return row 2
• When the page is loaded with ID=8 it returns row 8 (because 8 does not exist in the old_id column)
• When the page is called with ID=5 it returns nothing (no match in either column)

What have I tried:

I have tried various ways of qualifying this but can’t find the right syntax:

(first attempt is silly)

Attempted:

WHERE 
    table.old_id = :id 
    OR (table.new_id = :id AND table.old_id != :id) #bad one. 


WHERE 
    table.old_id = :id 
    OR (table.new_id = :id AND :id NOT IN (SELECT old_id FROM table)) 

WHERE 
    table.old_id = :id 
    OR (table.new_id = :id AND table.new_id NOT IN (SELECT old_id FROM table)) 
-- I think equivalent to the above 

WHERE CASE WHEN table.old_id = :id THEN true ELSE table.new_id = :id END

WHERE IF(table.old_id = :id, true, table.new_id = :id) 
-- I think equivalent to the above 

My Issue:

When an ID is found the SQL returns one row when it is found in the new_id only but otherwise it return both rows each time, when it should stop after a successful find in the old_id.

What am I missing; how can I get the SQL to check the old_id column and only if not found, then check the new_id column and only ever return one result?

What I’ve checked

• MySQL get rows but prefer one column value over another and
• using CASE in the WHERE clause
• and various others that don’t fit the concept; there are two successful matches, but the matches should be proritised by column.

Answer

Assuming that your query should always return just one record (which is how I understand your question), you can do condition ordering and limit 1:

select *
from mytable 
where :id in (old_id, new_id)
order by case when old_id = :id then 0 else 1 end
limit 1

If two records match, the conditional order by clause puts the record that matched on old_id on top. Then limit 1 eliminates the other match. If only one record matches, ordering does not matter, and it is retained.