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 liviuslivius@... [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.bury@... [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

_

_._,_.___._,___