How to display all the records from left table which is joined to another table

Question

3.00/5 (2 votes)

See more:

Respected sir,
I m developing windows application using vb.net with MS-Access as backend.
I want to generate sales report by selecting perticuler month
for that i m trying to develope sql query...

table : Stock
----------------------------------------------
id item      size unit  MRP
----------------------------------------------
1  abc        500 gm    120  
2  xyz        500 gm    180
3  pqr        150 gm     60
4  lmn         50 gm     20
5  pqr          1 kg    500
----------------------------------------------    

table : Sale
---------------------------------------------
sr   Billno  bill_Date    itemID  Qnty   Total
---------------------------------------------
1     123    14/11/2013    1      2      240
2     123    14/11/2013    3      2      120
3     123    14/11/2013    4      3       60
4     124    20/02/2013    5      2     1000
5     124    20/02/2013    3      5      300  
---------------------------------------------
 
I want to show required month's sales report like below
for example month=11

sr  ItemName size unit  MRP  totalQnty TotalAmt
--------------------------------------------------
1.    abc     500 gm    120     1         240
2.    xyz     500 gm    180     0           0
3.    pqr     150 gm     60     7         420
4.    lmn      50 gm     20     3          60
5.    pqr       1 kg    500     0           0
---------------------------------------------------

here i want to dislpay all the items present in stock and beside that how many items are sold in this perticular month,if that item is not sold in given month then it should be blank as i shown in above example sr. 5 pqr was sold in month no=2 and not in 11 so its showing zero..likewise...

what would be SQL query for this..

Please help me,
Thank you,..

Posted 4-Jan-13 19:38pm

Sunil Bansode

Add a Solution

Comments

Sandeep Mewara 5-Jan-13 2:21am

Have you tried to write the query by yourself - anything so far?

Sunil Bansode 5-Jan-13 9:58am

yes i have tried so much but didnt get proper result..

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tharaka MTR · Answer 1 · 2013-01-04T20:38:00

You have to use LEFT OUTER JOIN for this. Please try following

SQL

SELECT stock.item AS ItemName, stock.size,
stock.unit,stock.MRP,ISNULL(MonthSale.Qnty,0) AS totalQnty, ISNULL(MonthSale.Total,0) AS TotalAmt
FROM stock LEFT OUTER JOIN (SELECT sale.itemID,SUM(sale.Qnty) AS Qnty,SUM(sale.Total) AS Total
FROM sale
WHERE YEAR(sale.bill_Date)=2013 AND MONTH(sale.bill_Date)=11
GROUP BY sale.itemID, sale.billdate) MonthSale
on stock.id = MonthSale.itemID

spunnam · Answer 2 · 2013-01-04T22:51:00

Try this:

SQL

;WITH cte as
(SELECT t.id
,t.item
,t.size
,t.unit
,t.MRP
,CASE WHEN MONTH(a.bill_Date)=11 THEN sum(a.Qnty)
ELSE 0 END AS totalQnty
,CASE WHEN month(a.bill_Date) = 11 THEN sum(a.Total)
ELSE 0 END AS totalAmt,a.bill_Date
FROM Stock t
left JOIN sale a ON t.id = a.itemID
WHERE a.bill_Date IS NULL OR MONTH(a.bill_Date) = 11
GROUP BY a.itemID,t.item,t.size,t.unit,t.MRP,t.id,a.bill_Date)

SELECT s.item AS ItemName,
       s.size,
       s.unit,
       s.MRP,
       isnull(c.totalQnty,0) AS TotalQnty,
       isnull(c.totalAmt,0) AS TotalAmt
FROM Stock as s
left join cte as c ON s.id = c.id

Actually your result should be as follows for 11th month for your requirement:

sr ItemName size unit MRP totalQnty TotalAmt
-----------------------------------------------------------------------------
1. abc 500 gm 120 2 240
2. xyz 500 gm 180 0 0
3. pqr 150 gm 60 2 120
4. lmn 50 gm 20 3 60
5. pqr 1 kg 500 0 0
------------------------------------------------------------------------------

You incorrectly calculated totlaQnty of item 'abc' and TotalAmt of pqr.

Aarti Meswania · Answer 3 · 2013-01-04T23:17:00

Solution 3

This way...

SQL

select id,item,size,unit,MRP, sum(Qnty) as totalQnty, sum(Total) as TotalAmt
from stock s
left join Sale sl on s.id=sl.itemid
Where month(bill_Date) = 2
group by id,item,size,unit,MRP

Happy Coding!
:)

Posted 4-Jan-13 23:17pm

Aarti Meswania

Updated 4-Jan-13 23:19pm

v2

How to display all the records from left table which is joined to another table

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0