How to change TOP 1 query into general statement

SELECT TOP/FIRST 1 ROWS

Recently I was hit by interoperability problem: an application was to use both MSSQL and Firebird databases. And I was to check, whether there is a reference to a row, and if yes – mark this row.

Example: I have a table DRAWERS which keeps a bunch of drawers. And there’s ITEMS table, which holds items in drawers. I need to mark each drawer as empty or non empty.

Some attempts

First solution is inner sql statement to take top 1 row from ITEMS table, then ID from this row should be put into virtual field:

SELECT D.*,
(SELECT TOP 1 FROM ITEMS WHERE DRAWER_ID = D.ID) AS D.HAS_ITEMS
FROM DRAWERS D;

It works under MSSQL, but Firebird doesn’t understand such a request, Fb has it’s own syntax. Never mind, because general solution (as well for Oracle databases) for this case is:

SELECT D.*,
(SELECT MAX(ID) FROM ITEMS WHERE DRAWER_ID = D.ID) AS D.HAS_ITEMS
FROM DRAWERS D;

And now – if there’s any data in

D.HAS_ITEMS

– it means there is at least one item in this drawer.

Drawbacks

As we see – there is one additional select for each row.  It should be possible, to make a walk-around with joins. I just don’t know how to do this. Yet