August 11, 2008 at 6:35 am
Hello
Am using the following query, which is giving me duplicate records, can anyone please tell me what am doing wrong:
SELECT CONVERT(VARCHAR(20), T_OrderHeader.OrderDate, 106) AS [Order Date], T_OrderHeader.OrderID, T_OrderHeader.CustomerID,
T_OrderHeader.ShipTitle, T_OrderHeader.ShipForename, T_OrderHeader.ShipSurname, T_OrderHeader.OrderTotal, T_OrderHeader.OrderStatusID,
CONVERT(VARCHAR(20), T_Payments.ExpiryDate, 106) AS [Expiry Date]
FROM T_OrderHeader INNER JOIN
T_Payments ON T_OrderHeader.OrderID = T_Payments.OrderID INNER JOIN
T_OrderLine ON T_Payments.OrderID = T_OrderLine.OrderID INNER JOIN
T_Items ON T_OrderLine.StockID = T_Items.StockID
WHERE (T_Payments.ExpiryDate BETWEEN GETDATE() AND GETDATE() + 30) OR
(T_OrderHeader.OrderStatusID IN ('E', 'W')) OR
(T_Items.AvailableLev <= 0)
ORDER BY T_Payments.ExpiryDate DESC
August 11, 2008 at 6:41 am
First suspects:
does a Stock_ID uniquely identify a single row in T_Items?
Are Payments belonging to a single order only?
- Regards,
Andras
August 11, 2008 at 6:53 am
Thats right
Payments belong to a single order, with an Order having one or may stockIDs
August 11, 2008 at 7:00 am
b_boy (8/11/2008)
Thats rightPayments belong to a single order, with an Order having one or may stockIDs
It would probably help us a lot if you could post the primary keys/unique constraints on your four tables ( T_OrderHeader ,T_Payments, T_OrderLine and T_Items)
Regards,
Andras
August 11, 2008 at 7:13 am
These are the primary keys for the tables, and there are no constraints (sounds strange but thats they its set up)
T_OrderHeader - (PK, int, OrderID)
T_Payments -(PK, int, OrderID)
T_OrderLine - (PK, int, OrderID), (PK, int, LineID)
T_Items - (PK, int, StockID)(FK, int, SupplierID)
August 11, 2008 at 7:35 am
b_boy (8/11/2008)
These are the primary keys for the tables, and there are no constraints (sounds strange but thats they its set up)T_OrderHeader - (PK, int, OrderID)
T_Payments -(PK, int, OrderID)
T_OrderLine - (PK, int, OrderID), (PK, int, LineID)
T_Items - (PK, int, StockID)(FK, int, SupplierID)
For every line (LineID) in your order you will get a row in your joined set. You do some tests on them (T_Items.AvailableLev <= 0) but if you have multiple orderlines/items matching the (T_Items.AvailableLev <= 0) condition, you will get multiple lines in your result. Since you do not display any columns from the T_Items table (or the T_Order_Line), you will get duplicates.
There is a question about the (T_Items.AvailableLev <= 0) check. Do you want all the items in the order to be available? At the moment you are checking whether there is at least one item in the order that is available.
Regards,
Andras
August 11, 2008 at 7:53 am
The T_Items.AvailableLev <= 0 is the field within the table that informs end users of the available stock level “(back orders”- items where there is no stock available for shipping)
I have modified the query with the following:
WHERE (T_Payments.ExpiryDate BETWEEN GETDATE() AND GETDATE() + 30)
AND((T_OrderHeader.OrderStatusID IN ('E', 'W')) OR (T_Items.AvailableLev <= 0))
But I had to use the distinct keyword before the select word, (which is not what I want)
August 11, 2008 at 8:05 am
b_boy (8/11/2008)
The T_Items.AvailableLev <= 0 is the field within the table that informs end users of the available stock level “(back orders”- items where there is no stock available for shipping)I have modified the query with the following:
WHERE (T_Payments.ExpiryDate BETWEEN GETDATE() AND GETDATE() + 30)
AND((T_OrderHeader.OrderStatusID IN ('E', 'W')) OR (T_Items.AvailableLev <= 0))
But I had to use the distinct keyword before the select word, (which is not what I want)
You can move the check to the WHERE clause. So to check availability, instead of using the join with orderlines and items, you change your where clause to include "EXISTS (SELECT ... items that are not in stock) "
It will probably perform better than the DISTINCT
Regards,
Andras
August 11, 2008 at 8:56 am
Am not very good with subqueries, thats why i tend to avoid it, is there a way you can guide me through?
August 11, 2008 at 9:54 am
Got IT at last.
The subquery was far morw efficient than the Join, got more results than expected and there was no duplicate. Oooooooowwwwwwwweeeeeeeeeee!!!!!!!!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply