x

Sql Query Error


Select Product_Code,
 Isnull(Opening_stock,0) As OpeStock 
From Opening_Stock O 
Where Product_code=2 
Left Join
(Select Product_Code, 
  isnull(Sum(Quantity),0) RQ 
From Grn_Detail  R  
Where Product_Code=2 And Grn_Date <= '08/02/2012') 
 On R.Product_Code = O.ProductCode 
Left Join (Select Product_Code,
 Isnull(Sum(Quantity),0) Iq 
From Issue_Detail I  
Where  Product_Code =2 And Slip_Date <= '08/02/2012') On I.Product_code=O.Product_Code

more ▼

asked Feb 20, 2012 at 05:47 AM in Default

avatar image

Haren
51 10 10 11

It is giving an error of left join Please help

Feb 20, 2012 at 05:49 AM Haren
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The problem with your query was the wrong WHERE clause placement and not aliasing the subqueries output. I hope this will help

 SELECT  Product_Code
 ,       ISNULL(Opening_stock, 0) AS OpeStock
 FROM    Opening_Stock O
         LEFT JOIN ( SELECT  Product_Code
                     ,       ISNULL(SUM(Quantity), 0) RQ
                     FROM    Grn_Detail R
                     WHERE   Product_Code = 2
                             AND Grn_Date <= '08/02/2012'
                   ) R
         ON R.Product_Code = O.ProductCode
         LEFT JOIN ( SELECT  Product_Code
                     ,       ISNULL(SUM(Quantity), 0) Iq
                     FROM    Issue_Detail I
                     WHERE   Product_Code = 2
                             AND Slip_Date <= '08/02/2012'
                   ) I
         ON I.Product_code = O.Product_Code
 WHERE   O.Product_code = 2 

I personally like @Shawn Melton's coding approach as that is a much readable and easier format. But one cannot specify any filter in the main WHERE clause for the tables used in LEFT/RIGHT JOIN other than the table for which all rows to be returned. This would turn the LEFT/RIGHT JOIN to INNER JOIN functionality.

more ▼

answered Feb 20, 2012 at 06:51 AM

avatar image

Usman Butt
13.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left

I am no expert on T-SQL and my answer could very well be completely wrong. (Which if it is this will be a learning lesson for the both of us.)

This is just a best guess...


SELECT 
    o.Product_Code
    , OpeStock = ISNULL(o.Opening_stock,0)
    , RQ = ISNULL(SUM(r.Quantity),0)
    , IQ = ISNULL(SUM(i.Quantity),0)
From Opening_Stock AS o 
    LEFT JOIN Grn_Detail AS r ON r.Product_Code = o.Product_Code
    LEFT JOIN Issue_Detail AS i ON i.Product_Code = o.Product_Code
Where o.Product_code=2 
    AND r.Grn_Date 
more ▼

answered Feb 20, 2012 at 06:05 AM

avatar image

Shawn_Melton
6.4k 21 25 34

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x75

asked: Feb 20, 2012 at 05:47 AM

Seen: 800 times

Last Updated: Feb 20, 2012 at 06:51 AM

Copyright 2016 Redgate Software. Privacy Policy