SQL Query to get data from both tables

Hello there,

I am trying to get the sales qty and available stock from two tables. I am using this query and it is working but the problem i have is some products have available stock but no sales for a given period,therefore those products which have no sale for that preiod is getting skipped in the result of the query. How can I include both. I tried FULL JOIN but no luck.
 this is the section where i am combining the sales qty and available stock from these 2 tales
 FDOSales AS s
LEFT OUTER JOIN
    (
        SELECT
            SUM(dbo.ProductAvailable.AvailableQty) AS AvailableQty,
            dbo.ProductAvailable.ProductCode
        FROM
            ProductAvailable
        GROUP BY
            dbo.ProductAvailable.ProductCode ) AS pa
ON
    s.ProductCode = pa.ProductCode

Open in new window


and here is the complete query

SELECT
    spd.SupplierName,
    s.ProductNameEng,
    s.ProductCode,
    SUM(s.SalesQtyinclDis - s.SalesDiscount) AS SalesQty ,
    SUM(s.SalesDiscount)                     AS SalesDiscount ,
    (SUM(s.SalesQtyinclDis - s.SalesDiscount) * s.PurchasePrice) as SalesPurchase,
    s.SalesPrice,
    ISNULL(pa.AvailableQty,0) AS AvailableQty
FROM
    FDOSales AS s
LEFT OUTER JOIN
    (
        SELECT
            SUM(dbo.ProductAvailable.AvailableQty) AS AvailableQty,
            dbo.ProductAvailable.ProductCode
        FROM
            ProductAvailable
        GROUP BY
            dbo.ProductAvailable.ProductCode ) AS pa
ON
    s.ProductCode = pa.ProductCode
LEFT OUTER JOIN
    dbo.SupplierProductDetail AS spd
ON
    s.ProductCode = spd.ProductCode
WHERE
    s.SaleDate >= '20151023'
AND s.SaleDate <= '20151121'
AND s.SalesPrice IS NOT NULL
GROUP BY
    spd.SupplierName,
    s.ProductNameEng,
    s.ProductCode,
    s.PurchasePrice,
    s.SalesPrice,
    pa.AvailableQty
ORDER BY
    spd.SupplierName ASC,
    s.ProductNameEng ASC

Open in new window

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.

Karthik RamachandranCommented:
Use RIGHT OUTER JOIN.
0
zolfAuthor Commented:
I tried that too
0
Karthik RamachandranCommented:
You should remove the below condition as the Products will actually have SalesPrice as Null when there is no Sale at all.
AND s.SalesPrice IS NOT NULL

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zolfAuthor Commented:
I tried that also,you see it is this JOIN  i do between the FDOSales and ProductAvailable,like so
ON
    s.ProductCode = pa.ProductCode

which is not including the productCode from the pa(ProductAvailable)
0
Karthik RamachandranCommented:
Try this pls.
SELECT
    spd.SupplierName,
    s.ProductNameEng,
    pa.ProductCode,
    SUM(s.SalesQtyinclDis - s.SalesDiscount) AS SalesQty ,
    SUM(s.SalesDiscount)                     AS SalesDiscount ,
    (SUM(s.SalesQtyinclDis - s.SalesDiscount) * s.PurchasePrice) as SalesPurchase,
    s.SalesPrice,
    ISNULL(SUM(pa.AvailableQty),0) AS AvailableQty
FROM
    ProductAvailable AS pa LEFT OUTER JOIN
     AS FDOSales s
       ON pa.ProductCode = s.ProductCode LEFT OUTER JOIN
    dbo.SupplierProductDetail AS spd
       ON pa.ProductCode = spd.ProductCode
WHERE
    s.SaleDate >= '20151023'
AND s.SaleDate <= '20151121'
GROUP BY
    spd.SupplierName,
    s.ProductNameEng,
    pa.ProductCode,
    s.PurchasePrice,
    s.SalesPrice,
    pa.AvailableQty
ORDER BY
    spd.SupplierName ASC,
    s.ProductNameEng ASC

Open in new window

0
zolfAuthor Commented:
Running this query gives me all wrong values also those products are still missing which was my initial problem.
Please see my tables

1
2
0
zolfAuthor Commented:
I think it is that date that is not letting those products to show here avaiableQty.
0
Karthik RamachandranCommented:
Remove "pa.AvailableQty" from the GROUP BY clause in the query I gave you earlier.
0
zolfAuthor Commented:
No luck.i did not expect it to be so complex
0
zolfAuthor Commented:
1
0
Karthik RamachandranCommented:
Can you remove the below WHERE clause, run the query with RIGHT OUTER and see if it returns the expected data. There is no issue with the date condition. It looks good. Just try this.
WHERE
    s.SaleDate >= '20151023'
AND s.SaleDate <= '20151121'
AND s.SalesPrice IS NOT NULL

Open in new window

0
zolfAuthor Commented:
can you please give me the query again,so i can be sure it is as you want it
0
Karthik RamachandranCommented:
SELECT
    spd.SupplierName,
    s.ProductNameEng,
    s.ProductCode,
    SUM(s.SalesQtyinclDis - s.SalesDiscount) AS SalesQty ,
    SUM(s.SalesDiscount)                     AS SalesDiscount ,
    (SUM(s.SalesQtyinclDis - s.SalesDiscount) * s.PurchasePrice) as SalesPurchase,
    s.SalesPrice,
    ISNULL(pa.AvailableQty,0) AS AvailableQty
FROM
    FDOSales AS s
RIGHT OUTER JOIN
    (
        SELECT
            SUM(dbo.ProductAvailable.AvailableQty) AS AvailableQty,
            dbo.ProductAvailable.ProductCode
        FROM
            ProductAvailable
        GROUP BY
            dbo.ProductAvailable.ProductCode ) AS pa
ON
    s.ProductCode = pa.ProductCode LEFT OUTER JOIN
    dbo.SupplierProductDetail AS spd
ON
    pa.ProductCode = spd.ProductCode
GROUP BY
    spd.SupplierName,
    s.ProductNameEng,
    s.ProductCode,
    s.PurchasePrice,
    s.SalesPrice,
ORDER BY
    spd.SupplierName ASC,
    s.ProductNameEng ASC

Open in new window

0
zolfAuthor Commented:
I had to add pa.AvailableQty in the GROUP BY as i have this is the select and it would not let me run the query without it.

Running the query gives me all the products with availableqty and their sales but without the date condition which is my requirement

1
0
Kevin CrossChief Technology OfficerCommented:
zolf, it appears this is a duplicate of your other question (http://www.experts-exchange.com/questions/28826260/SQL-Query-Help.html), so please see my answer there.  You need to make the ProductDetail table the focus (left-most table) of the query then LEFT OUTER JOIN derived queries summing sales and availableQty separately.  This will give you sales for the period if it exists, quantity for period if it exists, and combination when you have both.  If you only want to see items with availableQty, you can use an INNER JOIN but, as I said, my suspicion is that table has many rows for the same product because of the batch number column.
1
Kevin CrossChief Technology OfficerCommented:
P.S. with the derived table approach, you can include the date and other filtering in the inner queries.  If you need data from the outer table, you would place the criteria in the ON versus WHERE clause as any conditions in the WHERE clause will turn your OUTER JOIN statements into INNER JOIN ones since the comparison will eliminate NULLs.  OUTER APPLY also may be a good option, but I would start with an aggregate query grouped on the key fields on the inside  and JOIN to outer query on the key fields.
1

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
PortletPaulfreelancerCommented:
This is a duplicate question as far as I can see, and the same lack of information exists here. For us to help you resolve this question we NEED information about the SOURCE DATA and we need an "EXPECTED RESULT"

Despite this lack of information I do believe Kevin Cross is on the right path. Aggregate in subqueries, then use left outer join these to the main query.

HOWEVER it looks like you might need a table that you haven't used so far.
This table needs to be the "master list of products" I will call this "Product_Master". i.e. this table needs all product codes regardless of if they have been sold or if they are available.

If such a table exists then overall query structure would then look like this

e.g.
Product_Master
productcode, productname
A, Alpha product
B, Bravo product
C, Charlie product
D, Delta product

SalesData (subquery result)
productcode, soldqty
A, 1000
C, 1000

AvailableData (subquery result)
productcode, availableqty
A, 2000
B, 1000
C, 500

select
        p.productcode, p.productname
      , ISNULL(s.soldqty,0) as sales
     , ISNULL(a.availableqty,0) as availableqty
from Product_Master as P

LEFT OUTER JOIN (
                                -- subquery for sales
                                ) as  S
                                             ON P.productcode = S.productcode

LEFT OUTER JOIN (
                               -- subquery for available quantty
                               ) as A
                                             ON P.productcode = A.productcode
Result would be:
p.productcode p.productname,  s.soldqty, a.availableqty
A             Alpha product       1000            2000
B             Bravo product          0            1000
C             Charlie product     1000             500
D             Delta product          0               0

Open in new window

1
zolfAuthor Commented:
cheers
0
zolfAuthor Commented:
Paul, thanks for your feedback. Appreciate your help but i accepted the answer and then I saw your feedback. Sorry about it!!
0
PortletPaulfreelancerCommented:
No problem, Kevin saw the solution well before I did, I was just tying to reinforce it.

I would really suggest you try this for future questions:

a. supply a small sample of source data (not images, not query results)
b. provide the "expected result"
0
zolfAuthor Commented:
thanks,I will!!
0
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
Microsoft SQL Server

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.