Hi All,

I have a question which I wonder could someone help me with... I think it is quite complex , but am hoping someone could point me in the right direction.

I need to extract all items (stockItem table) where there have been no sales order (SOPOrderReturn --> Linked to SOPOrderReturnLine -- which holds the stock item)for particular customers....so all items I need to extract and customers that have never brought the goods.

The tables concerned are

SLCustomerAccount -->Primary Key SLCustomerAccountID

SOPOrderReturn-->Primary Key = SOPOrderReturnID Foreign Key = CustomerID (SLCustomerAccountID)

SOPOrderReturnLine --> ForeignKey SOPOrderReturnID(SOPOrderReturnID), ItemCode = StockItem.Code

StockItem -> StockItem.Code = SOPOrderReturnLine.ItemCode.

I hope that I am making sense.....I attach a screen shot of the tables concerned , it might explain it better.

Thanks a million for any information.

Best Regards

Aidan

Hi aidan1972,

Let's just put the non-dependent columns aside, the dependency between each table can be descripted as they are in the below example.

CREATE TABLE StockItem
(itemID INT PRIMARY KEY,
 code INT UNIQUE);

CREATE TABLE SLCustomerAccount
(
SLCustomerAccountID INT PRIMARY KEY
);

CREATE TABLE SOPOrderReturn
(
SOPOrderReturnID INT PRIMARY KEY,
CustomerID INT,
CONSTRAINT fk_cID_SLCA FOREIGN KEY (CustomerID) REFERENCES SLCustomerAccount(SLCustomerAccountID)
);

CREATE TABLE SOPOrderReturnLine
(
SOPOrderReturnLineID INT PRIMARY KEY,
SOPOrderRetrunID INT,
ItemCode INT,
CONSTRAINT fk_SOPORID_SOPOR FOREIGN KEY (SOPOrderRetrunID) REFERENCES SOPOrderReturn(SOPOrderReturnID),
CONSTRAINT fk_itemCode_SOPOR FOREIGN KEY (ItemCode) REFERENCES StockItem(code)
);

All items there have been no sales order for particular customers.

SELECT * FROM StockItem
WHERE code NOT IN(
SELECT sorl.itemcode FROM SLCustomerAccount slca
					JOIN SOPOrderReturn sor
						ON slca.SLCustomerAccountID = sor.CustomerID
					JOIN SOPOrderReturnLine sorl
						ON sor.SOPOrderReturnID = sorl.SOPOrderRetrunID
			WHERE slca.SLCustomerAccountID IN (1001,1002) --particular customers id
);

Customers that have never brought the goods.

SELECT * FROM SLCustomerAccount
WHERE SLCustomerAccountID NOT IN(
SELECT DISTINCT CustomerID FROM SOPOrderReturn
);

If you have any question, feel free to le