Re: [firebird-support] Left join derived table

['Arno Brinkman' fbsupp...@abvisie.nl [firebird-support]]

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


  ._,___