-1

I want to return ALL RECORDS from the MillInvoice table regardless if there is a corresponding record in the PriceIncrease Table. When I run the query below I am getting just those records in the MillInvoice table that have a corresponding PriceIncrease record.

SELECT MILLInvoices.*,
       PriceIncrease.PriceIncreaseId,
       PriceIncrease.PriceForecasted,
       PriceIncrease.DateForecasted 
FROM MILLInvoices MILLInvoices
     LEFT OUTER JOIN PriceIncrease PriceIncrease
         ON MILLInvoices.MillCode = PriceIncrease.Plant
            AND MILLInvoices.ParentNumber = PriceIncrease.Mapa8
            AND MILLInvoices.Segment = PriceIncrease.GradeSegment
            AND MILLInvoices.City = PriceIncrease.City
            AND MILLInvoices.State = PriceIncrease.State
            AND MILLInvoices.Zip = PriceIncrease.ZipCode
Tom Ritter
  • 1
  • 1
  • Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. Also this is a faq. Also please read & act on [mcve]. Including giving your SQL/DBMS. – philipxy Aug 04 '18 at 21:58
  • Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) – philipxy Aug 04 '18 at 22:04
  • Please don't edit a question in a way that invalidates a reasonable answer. Although here your question was (& is) unclear so it shouldn't have been answered. Eg now that answer makes no sense. If there is a reasonable answer, edit your question to say what you were trying to say, and post a new question for the new question with new code. – philipxy Aug 05 '18 at 02:26

1 Answers1

0

I'm not sure how the tables or the data model looks like, but from your questions I'm guessing what you are trying to do here is to include records from MILLInvoices where no matching records in PriceIncrease exist. In this case the result will have empty fields under the columns that were retrieved from the table PriceIncrease. The query might be doing what you want until you get to the WHERE part. The WHERE part pretty much excludes any records from the result where columns from PriceIncrease have empty values or values that don't comply with conditions of the WHERE statement. Try removing the last part WHERE part if you want all MILLInvoices to appear in the result.

If you want row that match the criteria plus rows to which no corresponding records in PriceIncrease try something like this:

WHERE (PriceIncrease.BusinessUnit IS NULL AND PriceIncrease.PriceIncreaseNameId IS NULL) OR (PriceIncrease.BusinessUnit = 'Mill' AND PriceIncrease.PriceIncreaseNameId = 4)
admdev
  • 448
  • 2
  • 9
  • 1
    So what happens now? You no longer need the WHERE caluse? does the new query work and gives you what you need? I also think there are too many join conditions, usually join condition are one field which is a primary key in one table and a foreign key in the other, but here I can identify two unnecessary conditions which are city and state. I would think this should be taken care of by zipcode. Can you give more information about the nature of both tables and the relationship between them? – admdev Aug 04 '18 at 18:00
  • Please don't answer unclear questions, ask for clarification, don't guess. Here is the classic result--another unclear question version & answer that doesn't make sense with it. – philipxy Aug 05 '18 at 02:23
  • I saw a clear enough problem in the original question and I posted an answer. The fact that the OP changed the question to make the answer irrelevant is out of my control. – admdev Aug 05 '18 at 08:43