Board index » delphi » SQL Problem ...

SQL Problem ...

I am using the following SQL statement:

SELECT DISTINCT estimate.JobID, estimate.JobName,
    estimate.JobCode, estimate.GeneralContractor,
    estimate.EstimateDate, compinfo.CompanyID,
    compinfo.Company
FROM "estimate.db", "compinfo.db"
WHERE estimate.GeneralContractor = compinfo.CompanyID
ORDER BY estimate.JobName, estimate.EstimateDate

The only problem with this statement is the case where
there is no entry in "compinfo.db" for "estimate.GeneralContractor".

Can I say something like ....

WHERE estimate.GeneralContractor = compinfo.CompanyID
  OR {estimate.GeneralContractor is not in compinfo.db}

Thanks.
ryan

--
....................................................................
     Ryan Kenneth Williams           |      gt03...@prism.gatech.edu
Philippians 2:11  ...and that every tongue should confess that Jesus
                    Christ is Lord, to the glory of God the Father.

 

Re:SQL Problem ...


gt03...@prism.gatech.edu (Ryan K. Williams) wrote:

Quote
>I am using the following SQL statement:
>SELECT DISTINCT estimate.JobID, estimate.JobName,
>    estimate.JobCode, estimate.GeneralContractor,
>    estimate.EstimateDate, compinfo.CompanyID,
>    compinfo.Company
>FROM "estimate.db", "compinfo.db"
>WHERE estimate.GeneralContractor = compinfo.CompanyID
>ORDER BY estimate.JobName, estimate.EstimateDate
>The only problem with this statement is the case where
>there is no entry in "compinfo.db" for "estimate.GeneralContractor".
>Can I say something like ....
>WHERE estimate.GeneralContractor = compinfo.CompanyID
>  OR {estimate.GeneralContractor is not in compinfo.db}

What you need to do is an OUTER JOIN. Try something like this:

... FROM estimate.db e LEFT OUTER JOIN compinfo.db c ON
e.GeneralContractor=c.CompanyID...

Good luck!

Amders

Re:SQL Problem ...


On 30 Sep 1996 17:36:41 -0400, gt03...@prism.gatech.edu (Ryan K. Williams)
wrote:

Quote

>I am using the following SQL statement:

>SELECT DISTINCT estimate.JobID, estimate.JobName,
>    estimate.JobCode, estimate.GeneralContractor,
>    estimate.EstimateDate, compinfo.CompanyID,
>    compinfo.Company
>FROM "estimate.db", "compinfo.db"
>WHERE estimate.GeneralContractor = compinfo.CompanyID
>ORDER BY estimate.JobName, estimate.EstimateDate

FROM "estimate.db" left outer join "compinfo.db"
     on estimate.GeneralContractor = compinfo.CompanyID

Bye |/      --\  |\/|     |\/|
Bye |\ino   --/  |  |auro |  |ariuzzo (ITALY)

Other Threads