Solved

# JOIN 4 tables and do calculations

Posted on 2008-11-07
207 Views
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.*,

g.CombOmschr,

h.Wanted,

k.Sailing,

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

FROM(SELECT

teststock.CombArtNr,

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

)l

LEFT JOIN (SELECT

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

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

FROM productlist

GROUP BY CombArtNr

)g

ON l.CombArtNr = g.CombArtNr

LEFT JOIN (SELECT

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

SUM(prdbesarch.qty) "Wanted"

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

GROUP BY CombArtNr

)h

ON l.CombArtNr = h.CombArtNr

LEFT JOIN (SELECT

DISTINCT(LocCombArtCd),

FROM partspending

WHERE Aantal - Received <> 0

GROUP BY LocCombArtCd

)k

ON k.LocCombArtCd = l.CombArtNr

ORDER BY l.CombArtNr
``````
0
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.

0

## Featured Post

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 (http://www.enterprise.efax.com), 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…