Crystal Reports-Joining tables if exists

Question

i have a problem, i have 4 tables that i require to join, i already did the join for 3 of them (lets call them table1, table2, table3), join until this point is working fine, the problem comes with the last table, here i need to ask if a record exist on table number 4 (which is a combination between foreign key 'the same used to join the other 3 tables plus one additional column value, which is not recorded for all records), then if record exists on table 4, i need to display its content 'one specific column from table4', otherwise just display it in blank. Has someone done this before?

This is the query with the first 3 tables join.

SELECT
TABLE1.NUMPROD as BATCH_ID,
TABLE2.EQPCODE as Tank,
TABLE1.CODEPROD as Product_Code,
TABLE1.XFIELD_03 as Sequence,
TABLE3.ITEM_CONSUMED as ITEM,
TABLE3.CONSUMPTION_QUANTITY as QUANTITY

FROM 
TABLE1 
INNER JOIN TABLE3 on TABLE1.NUMPROD = TABLE3.ORDERID
INNER JOIN TABLE2 ON TABLE3.ORDERID = TABLE2.NUMBERLOT

AND TABLE1.ETAT = 'F' 
AND TABLE1.DATECREATION BETWEEN ('20191001') AND ('20191004') 
AND TABLE1.XFIELD_03 IS NOT NULL
AND TABLE3.CONSUMPTION_QUANTITY > 0 
and TABLE2.VALUE = 'ASSIGNED'

Answer 1

How about something like this:

SELECT
TABLE1.NUMPROD as BATCH_ID,
...
TABLE3.CONSUMPTION_QUANTITY as QUANTITY,
--                                           --> from this line ...
(select some_column 
 from table4 t4
 where t4.some_id = table1.some_other_Id
) table_4_column                      
--                                           --> ... to this line
FROM 
TABLE1 JOIN ...

Query that fetches data from table4 must return at most one value; otherwise, you'll get too-many-rows error.