Hi, > SELECT A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2 > FROM TABLE_A A > LEFT JOIN (SELECT FIRST 1 D.FIELD_D1, D.FIELD_D2, D.FIELD_3 FROM TABLE_D D > ORDER BY D.ID DESC) D ON D.FIELD_3 = A.FIELD_3
I assume this is not what he wants. The reason is the FIRST 1 in the derived table! The (D.FIELD_3 = A.FIELD_3) clause will be executed after the FIRST 1 on the derived table is applied. If TABLE_D.ID is unique this could be a solution: SELECT A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2 FROM TABLE_A A LEFT JOIN TABLE_D D ON (D.ID = (SELECT FIRST D2.ID FROM TABLE_D D2 WHERE D2.FIELD_3 = A.FIELD_3 ORDER BY D2.ID DESC)) Kind Regards, Arno Brinkman ABVisie From: liviuslivius liviusliv...@poczta.onet.pl [firebird-support] Sent: Wednesday, September 28, 2016 12:39 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Left join derived table W dniu 2016-09-28 09:04:07 użytkownik 'Marcin Bury' marcin.b...@studio-delfi.pl [firebird-support] <firebird-support@yahoogroups.com> napisał: Hello All I'd like to ask how to get below statement working: SELECT A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2 FROM TABLE_A A LEFT JOIN (SELECT FIRST 1 FIELD_D1, FIELD_D2 FROM TABLE_D D WHERE D.FIELD_3 = A.FIELD_3 ORDER BY D.ID DESC) D ON (1=1) Firebird complains that A.FIELD_3: Column does not belong to referenced table. I have the 'one to many' relation between TABLE_A and TABLE_D, and I would like to join a record from TABLE_A with latest detail entry from TABLE_D. Maybe derived tables are not the right solution here. Thanks Marcin ._,___