Subject Re: [firebird-support] Left join derived table
Author Arno Brinkman
Post date 2016-09-28T11:30:24Z
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
 
 
 
Sent: Wednesday, September 28, 2016 12:39 PM
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

_
_._,_.___._,___