Hi all

See my query at the bottom, apart from the other joins as required I have joined it with uSettings (a self-joined) table. This uSettings table contains 2 record for the date range for which the records from the main table tRMTestCard are to be returned.

Of my 8 sample records in the tRMTestCard table this query is expected to return only one record. Instead it returns 18 records all containing this one records details.

Please suggest what needs to be done about this query to get the desired result of one record.

Thanks and regards.
Bhavbhuti

SELECT tRMTestCard.IID,
tRMTestCard.IPID,
tRMTestCard.CBK,
tRMTestCard.INO,
tRMTestCard.TDT,
tRMTestCard.ISGRNID,
tRMTestCard.CSTATUS,
tRMTestCard.ICONTROLNO,
tRMTestCard.CCONTROLPOSTFIX,
CAST(IIF(CHAR_LENGTH(TRIM(tGoodsRecvNote.CBK)) = 0, '', TRIM(tGoodsRecvNote.CBK) || '/') || CAST(tGoodsRecvNote.iNo AS VARCHAR(12)) AS CHAR(15)) AS csGRN,
TGOODSRECVNOTE.CBK AS CGRNBK,
TGOODSRECVNOTE.INO AS IGRNNO,
TGOODSRECVNOTE.TDT AS TGRNDT,
MACCOUNTS.CNAME AS CSUPPLIERNAME,
MLOOKUPS.VVALUE AS CSUPPLIERCITY,
MACCOUNTS.CCODE AS CSUPPLIERCODE,
MITEMS.CDESC AS CITEMDESC,
MITEMS.CPACK AS CITEMPACK,
MITEMS.CCODE AS CITEMCODE
FROM tRMTestCard tRMTestCard
LEFT JOIN SGOODSRECVNOTEITEM SGOODSRECVNOTEITEM
ON SGOODSRECVNOTEITEM.IID = tRMTestCard.ISGRNID
LEFT JOIN TGOODSRECVNOTE TGOODSRECVNOTE
ON TGOODSRECVNOTE.IID = SGOODSRECVNOTEITEM.IPID
LEFT JOIN MITEMS MITEMS
ON SGOODSRECVNOTEITEM.IITEMID = MITEMS.IID
LEFT JOIN MACCOUNTS MACCOUNTS
ON TGOODSRECVNOTE.ISUPPLIERID = MACCOUNTS.IID
LEFT JOIN MLOOKUPS MLOOKUPS
ON MACCOUNTS.ICITYID = MLOOKUPS.IID
JOIN USETTINGS USETTINGS
ON tRMTestCard.TDT BETWEEN (SELECT US1.DVALUE FROM USETTINGS US1 JOIN USETTINGS US2 ON US2.IID = US1.IPID WHERE US2.CKEY = 'Current Year' AND US1.CKEY = 'From')
AND (SELECT US3.DVALUE FROM USETTINGS US3 JOIN USETTINGS US4 ON US4.IID = US3.IPID WHERE US4.CKEY = 'Current Year' AND US3.CKEY = 'To')
ORDER BY tRMTestCard.TDT, tRMTestCard.CBK, tRMTestCard.INO