Hi Alexandre

Thanks for your quick response. Please correct me where I am wrong. My understanding of the WHERE clause is that it is client side filter or atleast it applies the filter after the dataset is created, sort of a step 2. So is it (as per my understanding) be efficient because it will create a set and then remove records, when I did not want the records in the first place.

Will what I have done (however unusual :)) have a bad effect on the performance of the query or load the server unnecessary?

I am open to do what you suggested but just want to clarify my understanding of what I have done.

Thanks and regards
Bhavbhuti

--- In firebird-support@yahoogroups.com, Alexandre Benson Smith <iblist@...> wrote:
>
> 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
>