February 26th, 2013, 04:29 PM
SQL query not working
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
(if there are typos, sorry I tried to translate it as readable into English as possible)
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