Good evening,

I have a SQL query where I have to query three tables (some of them twice) and I do not get the result I need:

I need to search in 'articles' for a search string ('tennis') and lookup a second table 'orderdetails' for these articles. The hits show how much was ordered.

Now I need to check if these items were already delivered. So I look up table 'orders' for the 'orderdetails' and look in the same table if these orders have a 'delivery'. 'orders' adn 'delivery' have a field which shows what kind it is.
So I have to check if a 'delivery' was forwarded by an 'order'.

Next check if this 'delivery' includes the 'article' and sum up the delivered articles.
If the number of delivered articles is lower than the number of ordered articles I want to show this record.

So far it worsk besides these items: - 'orders' without a 'delivery' are not shown at all - 'orders' with the same amount as 'delivery' are shown, but it is required that these are not shown.

Here is what I have so far:

PrO: Process Order
PrD: Process Delivery
a: ArticleItem
p: processOrderItem
d: deliveryItem

    a.Articlenumber AS Article,
    PrO.Number AS Order, 
    PrD.Number AS Delivery,
    p.Amount AS Orderamount,
    SUM(d.Amount) AS Deliveryamount,
    (p.Amount - Deliveryamount) AS OpenAmount

FROM Article AS a

    INNER JOIN ProcessesDetails AS p
    ON (a.ArticleNumber = p.Article)

    INNER JOIN Processes as PrO
    ON PrO.Number = p.Order

    LEFT JOIN Processes as PrD
    ON PrO.Nummer = PrD.ForwardedFrom

    INNER JOIN ProcessesDetails as d
    ON PrD.Number = d.Order

WHERE (a.Categorie = 'tennis') 
AND LEFT(p.Order, 3) = 'OR-'
AND TEXTSEARCH('Delivery:' IN PrO.Forwarded)
AND LEFT(PrD.Number,3) = 'DE-' 
AND d.Article = p.Article

GROUP BY(Article)
(if there are typos, sorry I tried to translate it as readable into English as possible)