Looking to Outer Join more than 2 tables

[RSchnable]

I believe this is where this question should be. I have data in an external DB that I use Excel and Microsoft Query to pull data from. Here is my current SQL query.

SELECT DISTINCT Customer_0.Name, CRMCall_0.SalesRepCode, CRMCall_0.OrigDate, CRMCall_0.CallText, CustCnt_0.Name, CRMCall_0.CallDesc
FROM PUB.CRMCall CRMCall_0, PUB.CustCnt CustCnt_0, PUB.Customer Customer_0
WHERE Customer_0.Company = CRMCall_0.Company AND Customer_0.CustNum = CRMCall_0.CallCustNum AND CustCnt_0.Company = CRMCall_0.Company AND CustCnt_0.Company = Customer_0.Company AND CustCnt_0.CustNum = Customer_0.CustNum AND CustCnt_0.ShipToNum = Customer_0.ShipToNum AND CustCnt_0.ConNum = CRMCall_0.CallConNum
ORDER BY CRMCall_0.OrigDate

Now I've tried using a LEFT OUTER JOIN, but I could not get it working. I essentially want all of my records from CRMCall. Then I link it to Customer to get Customer.Name. Then I link that to CustCnt to get CustCnt.Name. My dilema is that not every record in CRMCall has a CustCnt.Name record associated with it. I want to show all records in CRMCall, regardless if whether or not they have a CustCnt. If my CRMCall record has a CustCnt.Name, I want to display the name, if it does not, I want it to be blank, or NULL, or whatever other value.

Thanks in advance!

 

[bborissov]

The query you post here did not use LEFT join. It uses WHERE to join tables and that way you have INNER join.
Try:

SELECT DISTINCT 
       Customer_0.Name,
       CRMCall_0.SalesRepCode,
       CRMCall_0.OrigDate,
       CRMCall_0.CallText,
       CustCnt_0.Name,
       CRMCall_0.CallDesc
FROM PUB.CRMCall CRMCall_0
LEFT JOIN PUB.Customer Customer_0 
     ON Customer_0.Company = CRMCall_0.Company AND
        Customer_0.CustNum = CRMCall_0.CallCustNum
LEFT JOIN PUB.CustCnt CustCnt_0 
     ON CustCnt_0.Company   = CRMCall_0.Company    AND
        CustCnt_0.Company   = Customer_0.Company   AND
        CustCnt_0.CustNum   = Customer_0.CustNum   AND
        CustCnt_0.ShipToNum = Customer_0.ShipToNum AND
        CustCnt_0.ConNum    = CRMCall_0.CallConNum
ORDER BY CRMCall_0.OrigDate

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

And make a note to never use implicit syntax again. This is very poor programming practice.

"NOTHING is more important in a database than integrity." ESquared

 

[RSchnable]

Thanks Bor...I was working on another query using a LEFT join and got it working, so I should be able to get this one now.

And Sister, I assume when you say implicit syntax, you mean using the WHERE statement to get my desired results as opposed to using JOINs to link my tables?

 

[markros]

You should always use JOIN syntax. WHERE syntax of joining tables is outdated.

BTW, you may find this blog post

http://beyondrelational.com/blogs/n...-t-bring-all-records-from-the-left-table.aspx

helpful to avoid one very common mistake with JOINS.

PluralSight Learning Library

 

[SQLSister]

The where syntax is 18 years outdated. Plus it is very susceptible to accidental cross joins and the implied left join syntax is deprecated (as well as not working correctly even as far back as SQL Server 2000).

It is also harder to maintain (was that an accidental cross join or a delinerate one?).



"NOTHING is more important in a database than integrity." ESquared