Databases
StackOverflow2013
Postsdbo
POSQL query need nulls to appear
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL query need nulls to appear
    primarykey
    data
    text
    <p>Im using SQL Server manager 2008.</p> <p>I have a query as follows:</p> <pre><code>SELECT dbo.iLines.Part, dbo.iLines.Pg, SUM(dbo.iLines.Qty) as sales6months, dbo.iLines.Prefix FROM Autopart.dbo.iLines RIGHT JOIN dbo.product ON dbo.product.keycode = dbo.ilines.part where prefix = 'i' and ([datetime] &gt; dateadd(month, -6, getdate())) and dbo.ilines.part = 'BK939' group by dbo.ilines.pg, dbo.ilines.part, dbo.ilines.prefix order by sales6months desc </code></pre> <p>So to explain, I want to get the last 6month sales on all the products in the product table.</p> <p>The problem is the fact that some products wont have any sales. I still need them to show. So what I'm asking is to show the last 6month sales on ALL products including 0 sales.</p> <p>"iLines" is the table causing the problem since the part number only exists in there if there has been a sale.</p> <p>I know there may be a way doing multiple query's etc. But I need this all in 1 query ONLY.</p> <p>I tried letting null through but does nothing plus its really scary using nulls hehe.</p> <p>Any code snippet to add to this query would be super awesome!!</p> <p>Many thanks!</p> <p>Also if you need any more info just ask!</p> <p>UPDATE! Yes sorry Datetime only exists in Ilines.</p> <p>Ilines = table of sales Product = just a table of all our products</p> <p>Heres the main query, its meant to pull the top "n" sold parts in the last 6 months. It works except like I said it doesn't show parts that have not sold.</p> <pre><code>ALTER PROCEDURE [dbo].[MyPareto] @pgParam varchar(255) AS SELECT i.pg, dbo.OldParetoAnalysis.Pareto, i.part, i.sales6months, a.LostSales6Months, dbo.NewParetoAnalysis.Pareto FROM OPENQUERY(SACBAUTO, 'SELECT dbo.iLines.Part, dbo.iLines.Pg, SUM(dbo.iLines.Qty) as sales6months, dbo.iLines.Prefix FROM Autopart.dbo.iLines where prefix = ''i'' and [datetime] &gt; dateadd(month, -6, getdate()) group by dbo.ilines.pg, dbo.ilines.part, dbo.ilines.prefix order by sales6months desc') i RIGHT JOIN dbo.OldParetoAnalysis on i.part collate SQL_Latin1_General_CP1_CI_AS = dbo.OldParetoAnalysis.Part INNER JOIN dbo.NewParetoAnalysis ON dbo.OldParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NewParetoAnalysis.Part LEFT JOIN OPENQUERY(SACBAUTO, 'SELECT dbo.aLines.Part, dbo.aLines.Pg, SUM(dbo.aLines.Qty) as LostSales6Months, dbo.aLines.Prefix FROM Autopart.dbo.aLines where prefix = ''d'' and [datetime] &gt; dateadd(month, -6, getdate()) group by dbo.alines.pg, dbo.alines.part, dbo.alines.prefix order by LostSales6Months desc') a ON dbo.NewParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = a.part WHERE i.pg = @pgParam GROUP BY i.pg, dbo.OldParetoAnalysis.Pareto, i.part, i.sales6months, a.LostSales6Months, dbo.NewParetoAnalysis.Pareto ORDER BY dbo.OldParetoAnalysis.Pareto asc </code></pre> <p>think of the pareto as a league of the best parts. Hopefully this will help, I tried to avoid adding it as it may put some people off commenting.</p> <p>Ok new update, this open query works!!!</p> <pre><code>SELECT dbo.product.Keycode, dbo.iLines.Pg, SUM(COALESCE(dbo.iLines.Qty, 0)) as sales6months, dbo.iLines.Prefix FROM Autopart.dbo.product LEFT OUTER JOIN Autopart.dbo.ilines ON dbo.product.keycode = dbo.ilines.part AND ([datetime] &gt; dateadd(month, -6, getdate()) OR [datetime] is null ) WHERE (dbo.iLines.Prefix = 'i' OR dbo.iLines.Prefix is null) AND dbo.product.keycode = 'BK939' group by dbo.ilines.pg, dbo.product.keycode, dbo.ilines.prefix order by sales6months desc# </code></pre> <p>BUT when i then join with my pareto table as follows:</p> <pre><code>SELECT i.pg, dbo.OldParetoAnalysis.Pareto, i.keycode, i.sales6months FROM OPENQUERY(SACBAUTO, 'SELECT dbo.product.Keycode, dbo.iLines.Pg, SUM(COALESCE(dbo.iLines.Qty, 0)) as sales6months, dbo.iLines.Prefix FROM Autopart.dbo.product LEFT OUTER JOIN Autopart.dbo.ilines ON dbo.product.keycode = dbo.ilines.part AND ([datetime] &gt; dateadd(month, -6, getdate()) OR [datetime] is null )/* must be this*/ WHERE (dbo.iLines.Prefix = ''i'' OR dbo.iLines.Prefix is null) group by dbo.ilines.pg, dbo.product.keycode, dbo.ilines.prefix order by sales6months desc') i LEFT JOIN dbo.OldParetoAnalysis on i.keycode collate SQL_Latin1_General_CP1_CI_AS = dbo.OldParetoAnalysis.Part WHERE i.pg = '40' AND i.keycode = 'BK939' </code></pre> <p>The results go back the same, So this means the problem is when i go to join, BUT old pareto does contain the part number, and ive also tried changing the joins....Im hoping this has narrowed down the search for the problem and im Hoping some one has an idea why this is happening!</p> <p>FINAL UPDATE! Wow this is looong, but finally i figured out the problem, I had to recheck using the product tables PG field!!!!! since it wont be null!! here's the code!</p> <pre><code>ALTER PROCEDURE [dbo].[MyPareto32TEST] @pgParam varchar(255) AS SELECT i.pg, dbo.OldParetoAnalysis.Pareto, i.keycode, i.sales6months, a.LostSales6Months, dbo.NewParetoAnalysis.Pareto FROM OPENQUERY(SACBAUTO, 'SELECT dbo.product.Keycode, dbo.iLines.Pg, dbo.product.pg as ppg, SUM(COALESCE(dbo.iLines.Qty, 0)) as sales6months, dbo.iLines.Prefix FROM Autopart.dbo.product LEFT OUTER JOIN Autopart.dbo.ilines ON dbo.product.keycode = dbo.ilines.part AND ([datetime] &gt; dateadd(month, -6, getdate()) OR [datetime] is null ) WHERE (dbo.iLines.Prefix = ''i'' OR dbo.iLines.Prefix is null) group by dbo.ilines.pg, dbo.product.keycode, dbo.ilines.prefix, dbo.product.pg order by sales6months desc') i RIGHT JOIN dbo.OldParetoAnalysis on i.keycode collate SQL_Latin1_General_CP1_CI_AS = dbo.OldParetoAnalysis.Part AND (i.pg = @pgParam or (i.pg is null AND i.ppg = @pgParam)) INNER JOIN dbo.NewParetoAnalysis ON dbo.OldParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NewParetoAnalysis.Part LEFT JOIN OPENQUERY(SACBAUTO, 'SELECT dbo.product.Keycode, dbo.aLines.Pg, SUM(COALESCE(dbo.aLines.Qty, 0)) as lostsales6months, dbo.aLines.Prefix FROM Autopart.dbo.product LEFT OUTER JOIN Autopart.dbo.alines ON dbo.product.keycode = dbo.alines.part AND ([datetime] &gt; dateadd(month, -6, getdate()) OR [datetime] is null ) WHERE (dbo.aLines.Prefix = ''d'' OR dbo.aLines.Prefix is null) group by dbo.alines.pg, dbo.product.keycode, dbo.alines.prefix order by lostsales6months desc') a ON dbo.NewParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = a.keycode WHERE(i.pg = @pgParam or (i.pg is null AND i.ppg = @pgParam) AND dbo.NewParetoAnalysis.Pareto is not null) GROUP BY i.pg, dbo.OldParetoAnalysis.Pareto, i.keycode, i.sales6months, a.LostSales6Months, dbo.NewParetoAnalysis.Pareto ORDER BY dbo.OldParetoAnalysis.Pareto asc </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USArion
    1. USlemunk
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL query need nulls to appear
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL query need nulls to appear
      1. USlemunk
      1. VTBountyStart
    3. VO
      singulars
      1. POSQL query need nulls to appear
      1. This table or related slice is empty.
      1. VTUpMod
    1. COUPDATED: still no luck, tried most posts on here, tried combining some, what's very strange is the data I receive is Always the same.
      singulars
      1. POSQL query need nulls to appear
      1. USlemunk
    2. COUpdated response: note it helps if group by/ select contain an element from the part table, otherwise the right join isn't going to do much..
      singulars
      1. POSQL query need nulls to appear
      1. USxQbert
    3. COUPDATED my code, almost there!!!
      singulars
      1. POSQL query need nulls to appear
      1. USlemunk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload