tags:

views:

44

answers:

1
+1  Q: 

SQL Fallback Row?

I'm using MySQL 5.1 with PHP and I was wondering if it would be possible to run a query that will select a specific row using the primary key, but if it doesn't exist, to return a different one.

For example: select * from table1 where id="a" else id="b"

+1  A: 
select *
  from table1 
  where id="a"
union all
select * 
  from table1
  where id="b"
  and no exists (
    select * 
      from table1
      where id="a");
Remus Rusanu
This actually does 3 SELECTs. It would be faster to just do a UNION between 2 SELECTS and then LIMIT 1 on the final set.
Artem Russakovskii
@Artem: no, because the order of UNION is not guaranteed, so the LIMIT may result in the "b" one.
Remus Rusanu
Less than three select's would be a nice bonus, but still it works perfectly! Thanks =)
Remus, you might be right. In this case, you should be able to do something like this: `(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)UNION(SELECT 2 as sort_col, col2a, col2b, ... FROM t2) ORDER BY sort_col;` This is from http://dev.mysql.com/doc/refman/5.0/en/union.html.
Artem Russakovskii
@Artem: right, that would work better
Remus Rusanu