Bhavbhuti Nathwani wrote:

> Hi all
>
> I would like to bring in data from a table that does not have a corresponding FK in another table What I have currently done (see below) is to join with this another table and do a <> comparision. Is this fine or there is a more efficient way to do this.
>
> Thanks and regards.
> Bhavbhuti
>
> SELECT CAST(tBOMIssue.iID AS CHAR(12)) AS ctBMRID,
> tBOMIssue.CBK AS cBMRBk,
> ... more fields
> FROM tBOMIssue tBOMIssue
> one join here...
> JOIN tWastedChit
> ON tBOMIssue.iID <> tWastedChit.itBMRID
> ORDER BY tBMRDt, cBMRBk, iBMRNo
>

The usual method to do this is to use a LEFT OUTER JOIN and filter the
result set to return only the NULL's of the "right" table

SELECT CAST(tBOMIssue.iID AS CHAR(12)) AS ctBMRID,
tBOMIssue.CBK AS cBMRBk,
... more fields
FROM tBOMIssue tBOMIssue
one join here...
LEFT JOIN tWastedChit
ON tBOMIssue.iID = tWastedChit.itBMRID
ORDER BY tBMRDt, cBMRBk, iBMRNo
WHERE
tWastedChit.itBMRID is null


see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br