-2

here is the code for my view that calls all rows from the tables:

SELECT
dbo.Purchase_Order.Purchase_Order_ID, dbo.Purchase_Order.Purchase_Order_Number, dbo.Purchase_Order.Purchase_Order_Date, dbo.Purchase_Order.Special_Instructions, dbo.Purchase_Order_Items.Qty_Ordered, dbo.Purchase_Order_Items.Work_Order_Number, 
dbo.Purchase_Order_Items.Part_Discount, dbo.Purchase_Order_Items.Part_List_Price, dbo.Vendor.Vendor_Name, dbo.Ship_Method.Ship_Method, 
dbo.Ship_Method.Ship_Method_Acct_Number, dbo.Inventory.Part_Number, dbo.Inventory.Part_Description, dbo.Vendor_Location.Vendor_Address, 
dbo.Vendor_Location.Vendor_City, dbo.Vendor_Location.Vendor_State_Prov, dbo.Vendor_Location.Vendor_Zip_Country_Code, 
dbo.Vendor_Location.Vendor_Phone_800, dbo.Vendor_Location.Vendor_Phone_Local, dbo.Vendor_Location.Vendor_Address_2, 
dbo.Vendor_Location.Vendor_Country, dbo.Vendor_Location.Vendor_Fax
FROM
dbo.Purchase_Order INNER JOIN
dbo.Purchase_Order_Items ON dbo.Purchase_Order.Purchase_Order_ID = dbo.Purchase_Order_Items.Purchase_Order_ID INNER JOIN
dbo.Vendor ON dbo.Purchase_Order.Vendor_ID = dbo.Vendor.Vendor_ID INNER JOIN
dbo.Ship_Method ON dbo.Purchase_Order.Ship_Method_ID = dbo.Ship_Method.Ship_Method_ID INNER JOIN
dbo.Inventory ON dbo.Purchase_Order_Items.Part_Number_ID = dbo.Inventory.Part_Number_ID INNER JOIN
dbo.Vendor_Location ON dbo.Vendor.Vendor_ID = dbo.Vendor_Location.Vendor_ID

Then I add another table and then I only get 2 rows in my view when there should be 115 rows:

the following line is added the end of the FROM statement

dbo.Customer ON dbo.Purchase_Order.Customer_ID = dbo.Customer.Customer_ID

any ideas as to why? is there a limit to how many tables can be in a view?

thanks for looking Dave

Thanks for the help from all the attitude. for those that would really like to know the answer, this line fixed the join problem.

dbo.Customer ON dbo.Customer_Ship.Customer_ID = dbo.Customer.Customer_ID RIGHT OUTER JOIN
  • 3
    There is a thing called "aliases". I've heard it's good for making queries readable. – ypercubeᵀᴹ Jul 17 '15 at 15:05
  • 2
    As for your problem, it's rather obvious. Some of the rows in Purchase_Order do not have a Customer_ID recorded (they have NULL). You can use LEFT JOIN Customer ... instead and the result will go back to 115 rows. – ypercubeᵀᴹ Jul 17 '15 at 15:07
  • even if Purchase_Order.Customer_ID is null the rest of the rows in the table shouldn't be eliminated in the view should it? I will try the LEFT JOIN right after my meeting – Dave Hampel Jul 17 '15 at 15:07
  • 1
    I think you need to understand the basic concepts of JOINs – Mark Sinkinson Jul 17 '15 at 15:14
  • why have I been down voted? I thought this site is to get help and learn something. I have been studying the concepts of JOIN and I am sure that I will figure it out. it truly amazes me how much attitude there is on these sites. this is why I very seldomly ask anything here. when I created the VIEW I was using the designer in SQL Server by adding tables to the VIEW and choosing the fields that I would like to display. you are correct about me not knowing a few things like the concept of JOIN but studying and the comment on "aliases" doesn't help at all. Could you demonstrate aliases? – Dave Hampel Jul 18 '15 at 1:36
0

after additional research I discovered that the real answer for my situation was the use of RIGHT OUTER JOIN like this:

dbo.Customer ON dbo.Customer_Ship.Customer_ID = dbo.Customer.Customer_ID RIGHT OUTER JOIN

my last comment also has a link to the page that helped me out the most

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.