SQL query help

Hello there,

I am trying to come up with a report and there are 6 column which I need to show the client. I am using MSSQL 2008.
1.Product Code
2. Province Code
3. Sales Qty + offer Qty
4. offer qty
5. Returned Qty
6. returned offer qty

Now I wrote a query which gives me the first 4 columns (25656 rows) but when I try to add the query to return the 5th col it only returns the records which have the returned qty (7820 rows) and does not return the other sales record which did not have returned qty. How can I achieve this. I am also prepared to use SSIS if I need to, if I am not able to get it with SQL query.
Below is the original query which returns the first 4 cols correctly and I have marked the code in -- which I added later to give me the 5th col. Please help me to achieve my goal.

cheers
Zolf

SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + cast (dbo.Tafsil.code as  varchar(50)) AS code,
      dbo.Branch.NAME
    , dbo.Tafsil.description
    , CONVERT(VARCHAR(10), dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0), 105) AS saledate
    , sum(dbo.SaleDetail.quantity) AS soldQty,
    dbo.deliveryorderdetail.quantityDiscount
   -- ,dbo.SaleReturnDetail.quantity as ReturnedQtyt
FROM dbo.Batch
INNER JOIN dbo.Tafsil ON (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail ON (dbo.Batch.id = dbo.SaleDetail.batchId)
INNER JOIN dbo.Sale ON (dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN dbo.Receipt ON (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN dbo.Warehouse ON (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.Mohin ON  (dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN dbo.Kol ON (dbo.Mohin.kolId = dbo.Kol.id)
INNER JOIN
    dbo.DeliveryOrderdetail
ON
    (
        dbo.Saledetail.orderdetailId = dbo.DeliveryOrderdetail.id)
      --  INNER JOIN
    dbo.SaleReturn
ON
    (
        dbo.SaleReturn.saleId = dbo.Sale.id)
         INNER JOIN
    dbo.SaleReturnDetail
ON
    (
        dbo.SaleReturnDetail.saleReturnId = dbo.SaleReturn.id)--
WHERE dbo.Receipt.receiptDate >= '2015-06-22'
    AND dbo.Receipt.receiptDate <  '2015-07-22'
GROUP BY
      dbo.Branch.NAME
    , dbo.Tafsil.description
    , dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0),
    kol.code,
    Mohin.code,
    Tafsil.code,
    dbo.deliveryorderdetail.quantityDiscount,
    dbo.SaleReturnDetail.quantity
ORDER BY 
      dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0)

Open in new window


ee1
ee2
zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

I think you can use left outer join in place of inner join to the
dbo.SaleReturnDetail

SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + cast (dbo.Tafsil.code as  varchar(50)) AS code,
      dbo.Branch.NAME
    , dbo.Tafsil.description
    , CONVERT(VARCHAR(10), dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0), 105) AS saledate
    , sum(dbo.SaleDetail.quantity) AS soldQty,
    dbo.deliveryorderdetail.quantityDiscount
   -- ,dbo.SaleReturnDetail.quantity as ReturnedQtyt
FROM dbo.Batch
INNER JOIN dbo.Tafsil ON (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail ON (dbo.Batch.id = dbo.SaleDetail.batchId)
INNER JOIN dbo.Sale ON (dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN dbo.Receipt ON (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN dbo.Warehouse ON (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.Mohin ON  (dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN dbo.Kol ON (dbo.Mohin.kolId = dbo.Kol.id)
INNER JOIN
    dbo.DeliveryOrderdetail
ON
    (
        dbo.Saledetail.orderdetailId = dbo.DeliveryOrderdetail.id)
      --  INNER JOIN
    dbo.SaleReturn
ON
    (
        dbo.SaleReturn.saleId = dbo.Sale.id)
         LEFT OUTER JOIN
    dbo.SaleReturnDetail
ON
    (
        dbo.SaleReturnDetail.saleReturnId = dbo.SaleReturn.id)--
WHERE dbo.Receipt.receiptDate >= '2015-06-22'
    AND dbo.Receipt.receiptDate <  '2015-07-22'
GROUP BY
      dbo.Branch.NAME
    , dbo.Tafsil.description
    , dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0),
    kol.code,
    Mohin.code,
    Tafsil.code,
    dbo.deliveryorderdetail.quantityDiscount,
    dbo.SaleReturnDetail.quantity
ORDER BY 
      dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0)

Open in new window

0
Mike EghtebasDatabase and Application DeveloperCommented:
0
PortletPaulfreelancerCommented:
You are using INNER JOINS. This type of join INSISTS that BOTH tables MUST have matching values

There is a different family of joins, called OUTER JOINS, which allows only one table to have data

So, if you have a situation where you need data from a table, but only sometimes matching data in a related table, that join type requires an OUTER JOIN

The most commonly used outer join is LEFT OUTER JOIN e.g.

select *
from sales
LEFT OUTER JOIN returns on sales.id = returns.salesid
-- all sales are listed, but only some have returns


select *
from sales
INNER JOIN returns on sales.id = retruned.salesid
-- ONLY sales WITH returns are listed (because both tables MUST satisfy the join type)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.