Subject RE: [firebird-support] SQL Question
Author Leyne, Sean
Daniel,

> SELECT *
> FROM
> DETAIL
> INNER JOIN
> MASTER
> ON
> DETAIL.D_M_ID_1 = MASTER.M_ID
> WHERE
> DETAIL.D_M_ID_1 = :PARAMETER
>
>
>
> This may come back with tow records such as:
>
> D_ID | D_M_ID_1 | D_M_ID_2 | D_M_ID_3 | D_M_ID_4
> 1 | 2 | 3 | 4 | 2
> 2 | 3 | 7 | 5 | 9
>
>
>
> But rather than displaying the retrieved FK (M_ID) data for each and
every
> FK, I will like to display the M_Description data associated to the
> retried
> FK, so it would look like
>
> D_ID | D_M_ID_1 | D_M_ID_2 | D_M_ID_3 | D_M_ID_4
> 1 | desc_2 | desc_3 | desc_4 | desc_2
> 2 | desc_3 | desc_7 | desc_5 | desc_9

Try this:

SELECT
DETAIL.D_ID,
M1.M_Description,
M2.M_Description,
M3.M_Description,
M4.M_Description
FROM
DETAIL
Left JOIN MASTER M1 ON M1.M_ID = DETAIL.D_M_ID_1
Left JOIN MASTER M2 ON M2.M_ID = DETAIL.D_M_ID_2
Left JOIN MASTER M3 ON M3.M_ID = DETAIL.D_M_ID_3
Left JOIN MASTER M4 ON M4.M_ID = DETAIL.D_M_ID_4
WHERE
DETAIL.D_M_ID_1 = :PARAMETER


Sean