Joining several tables and views

  SELECT  dbo.Monitor_Request.WorkDesc
          , dbo.Monitor_Request.Request_ID
          , dbo.Monitor_Request.Due_Dt
          , dbo.Monitor_Request.Attempts
          , dbo.Monitor_Request.Status_Ind
          , dbo.Monitor_Request.Create_Dt
          , dbo.Monitor_Request.Monitor_ID
          , dbo.Monitor_Request.ByCustomer_ID
          , dbo.Monitor_Request.ByCompanyID
          , dbo.CompanyShim.Company_Name
          , dbo.PostalAddressShim.HouseName
          , dbo.PostalAddressShim.Street
          , dbo.PostalAddressShim.Town
          , dbo.PostalAddressShim.City
          , dbo.PostalAddressShim.County
          , dbo.PostalAddressShim.Postcode
    FROM  dbo.PostalAddressShim 
          RIGHT OUTER JOIN dbo.CompanyShim ON dbo.PostalAddressShim.Address_ID = dbo.CompanyShim.Company_Address_ID 
          RIGHT OUTER JOIN dbo.CUSTOMER ON dbo.PostalAddressShim.Address_ID = dbo.CUSTOMER.Address_ID 
          RIGHT OUTER JOIN dbo.Monitor_Request ON dbo.CUSTOMER.Customer_ID = dbo.Monitor_Request.ByCustomer_ID 
                                                  AND dbo.CompanyShim.Company_ID = dbo.Monitor_Request.ByCompanyID

I have created a view to display customer details with their addresses. A customer can be an individual or a company. They are stored in in different tables. For individuals the details are stored in a table called Customer and the company details are stored in the Company table. I am trying to create a view of all the customers in the database to display their addresses. When I join the tables separately I am getting the address details but if I am joining both the tables(Customer and Company) at once I am not getting null values for address details columns.

How can I get all the address details of the customers (Individual or company) from the database

---

I rarely found a need to use RIGHT OUTER JOIN and every time I encounter it, it forces me to think from right to left complicating things.

If I didn't goof up to badly, following joins should be equivalent to what you have written

SQL Statement

dbo.Monitor_Request mr 
LEFT OUTER JOIN dbo.Customer c ON c.Customer_ID = mr.ByCustomerID
LEFT OUTER JOIN dbo.CompanyShim cs ON cs.Company_ID = mr.ByCompanyID
LEFT OUTER JOIN dbo.PostalAddressShim pas ON pas.Address_ID = c.Address_ID
                                             AND pas.Address_ID = cs.Company_Address_ID

Now this is something I can read and theorize about. What's immediate obvious from this statement is the AND clause in joining the adresses with customers and companies effectively negating each other and returning no adresses at all.

My guess is you should simply

replace your RIGHT JOINS with LEFT JOINS

use OR instead of AND

SQL Statement

dbo.Monitor_Request mr 
LEFT OUTER JOIN dbo.Customer c ON c.Customer_ID = mr.ByCustomerID
LEFT OUTER JOIN dbo.CompanyShim cs ON cs.Company_ID = mr.ByCompanyID
LEFT OUTER JOIN dbo.PostalAddressShim pas ON pas.Address_ID = c.Address_ID
                                             OR pas.Address_ID = cs.Company_Address_ID

---

乍一看，尝试将您的RIGHT OUTER JOIN更改为LEFT OUTER JOIN 。

链接地址: http://www.djcxy.com/p/86288.html

上一篇: CASE在SQL Server中不起作用

下一篇: 加入多个表格和视图