JOIN 4 tables and do calculations

Posted on 2008-11-07
Last Modified: 2012-05-05
Hi everybody,

i'm stuck again with this SQL query.

I used to have this query where i would check the stock.
And compare it with our current pending orders from customers and get from the productlist the product description.

Now i've added a new table partspending to see what pending orders we have on production or sailing (not in stock)

So i can compare stock + sailing against pending orders customers

The code works well but the problem is it only checks the articles that are currently in stock.
If it's not on stock it doesnt show them.

I've been trying to change and adapt the SQL code to something else that works but i just can't seem to get it done.

Here is the code that works but isn't showing the articles that are not on stock.

SELECT l.*, 




CASE WHEN ISNULL(k.Sailing) = 1 THEN (h.Wanted-l.Allstock) ELSE (h.Wanted-(l.Allstock+k.Sailing)) END "Short"



    SUM(CASE teststock.KlantNr WHEN '0' THEN 0 ELSE 1 END) "Assigned", 

    SUM(CASE teststock.KlantNr WHEN '0' THEN 1 ELSE 0 END) "RestStock", 

    COUNT(teststock.CombArtNr) "AllStock" 

    FROM teststock 

    WHERE CombArtNr Like 'K%'

    GROUP BY teststock.CombArtNr 



CONCAT(productlist.artcode,'_',productlist.kleurcode) "CombArtNr", 

CONCAT(productlist.artomschr,' - ',productlist.artomschr2,' - ',productlist.kleuromschr) "CombOmschr" 

FROM productlist 



ON l.CombArtNr = g.CombArtNr 


CONCAT(prdbesarch.artcode,'_',prdbesarch.klrcode) "CombArtNr", 

SUM(prdbesarch.qty) "Wanted" 

FROM prdbesarch WHERE faktuurnr = '0' AND leveringsnr = '0'



ON l.CombArtNr = h.CombArtNr 



Aantal-Received "Sailing"

FROM partspending 

WHERE Aantal - Received <> 0  



ON k.LocCombArtCd = l.CombArtNr 

ORDER BY l.CombArtNr

Open in new window

Question by:24Carat
    1 Comment
    LVL 5

    Accepted Solution

    Since you have 4 left join, the query result will be limit by your first query

    Does the item that you are looking for are present in your L query? the field in this query talk about stock, then If they are not present in this query but they are in your G query put the G query first using a left join on the L query.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now