Dan Kennedy wrote:
On Fri, 2007-05-04 at 18:22 -0400, Vitali Lovich wrote:
Multiple tables contain a primary key KEY. If Table1 contains a matching KEY, then I want that row from Table1. Only if it isn't in Table1, then look in Table2 if it is there. If not in Table2 go on to Table3, etc etc.
How about this:

SELECT * FROM tbl1 WHERE key = $key UNION ALL SELECT * FROM tbl2 WHERE key = $key
  LIMIT 1;

Although technically, using LIMIT without ORDER BY is a bad thing.
The solution I came up with is:

SELECT coalesce(
(SELECT field1 FROM tbl1 WHERE key = $key),
(SELECT field1 FROM tbl2 WHERE key = $key),
(SELECT field1 FROM tbl3 WHERE key = $key))
, field2 FROM tbl1 WHERE key = $key;

The only problem with this though is that I can only select that 1 field
- if I want another, I have to do more select statements.  However, if
coalesce works the way I think it does, then it'll do early evaluation
and stop at the first non null parameter.  Also, I'm hoping that SQLite
realizes that it can retrieve field2 on its first evaluation of select.
Even if it can't though, I'm only expecting tbl1 to have at most maybe
10 entries.

Thoughts, suggestions?


To unsubscribe, send email to [EMAIL PROTECTED]

Reply via email to