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 '12 at 05:47 AM in Default

Haren gravatar image

51 8 10 11

It is giving an error of left join Please help
Feb 20 '12 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 '12 at 06:51 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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 <= '08/02/2012'  AND i.Slip_Date <= '08/02/2012' GROUP BY o.ProductCode 
more ▼

answered Feb 20 '12 at 06:05 AM

Shawn_Melton gravatar image

5.3k 17 21 29

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Feb 20 '12 at 05:47 AM

Seen: 593 times

Last Updated: Feb 20 '12 at 06:51 AM