SQL Query for getting item not sale to any customer

[mstrcmtr]

How can get the following result through SQL Query

1. Accounts Table

2. Item Table

3. Sale Table

How can prepare the list of customers to whom Item [A] NOT Sale

 

[Mike Lewis]

Difficult to answer your question without a lot more details. But, in general, if you want to get records from one table that are not matched in another table, you would do something like this:

SELECT * FROM Accounts WHERE ID [b]NOT IN[/b] (SELECT Account_ID FROM Items)

Do NOT copy that statement literally and expect it to work. It is just to give you a general idea.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

As Mike indicated without any idea of what each table has in it we can only guess.

With that in mind, I might guess something like

SELECT * FROM Accounts WHERE ID NOT IN (SELECT Account_ID FROM Sale)

Regardless if Mike's guess or mine is correct, you now have an idea of what YOU can do to get your own answer.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Aside of a NOT IN you can do clever joins.

CREATE CURSOR crsAccounts   (ID I Autoinc, Username Char(20))
CREATE CURSOR crsProducts   (ID I Autoinc, Productname Char(20))
CREATE CURSOR crsOrders     (ID I Autoinc, AccountID I)
CREATE CURSOR crsOrderItems (ID I Autoinc, OrderID I, ProductID I, Amount I)

INSERT INTO crsAccounts (Username)    VALUES ('user1')
INSERT INTO crsAccounts (Username)    VALUES ('user2')

INSERT INTO crsProducts (Productname) VALUES ('product1')
INSERT INTO crsProducts (Productname) VALUES ('product2')
INSERT INTO crsProducts (Productname) VALUES ('product3')
INSERT INTO crsProducts (Productname) VALUES ('product4')

INSERT INTO crsOrders     (AccountID) VALUES (1)
INSERT INTO crsOrderItems (OrderID, ProductID, Amount) VALUES (1,1,1)
INSERT INTO crsOrderItems (OrderID, ProductID, Amount) VALUES (1,3,2)

INSERT INTO crsOrders     (AccountID) VALUES (2)
INSERT INTO crsOrderItems (OrderID, ProductID, Amount) VALUES (2,2,4)
INSERT INTO crsOrderItems (OrderID, ProductID, Amount) VALUES (2,3,1)

SELECT Prd.ID as ProductID, Acc.ID As AccountID;
FROM      crsProducts   Prd ;
FULL JOIN crsAccounts   Acc ON .T. ;
LEFT JOIN crsOrderItems Itm ON Itm.ProductID = Prd.ID ;
LEFT JOIN crsOrders     Ord ON Itm.OrderID   = Ord.ID AND Ord.AccountID = Acc.ID ;
WHERE Prd.ID = 2;
GROUP BY Prd.ID, Acc.ID;
Having COUNT(Ord.ID) = 0 ;
INTO CURSOR crsResults

Let's look at this step for step. First data preparation tells us, we have 2 customers and 4 products, each customer had one order with two order items, in detail product 1 was only sold to user1, product 2 only to user2, product 3 to both, and product 4 was not sold at all.

The query does first combine any product with any customer account, as this is a full outer join with .T. as join condition, so we get the combination of all products and all customers. The Where clause limits that to only product ID 2, but if you remove that, you get the full blown list of combinations. This will get large for many products and customers, so be careful with that.

Anyway, next steps are to left join all orders via backwards going through order items and then orders, as we start the whole query from the products and not from the accounts, this is the natural order of joining. The tricky part of this is only joining the final order row if this order was from the right account. This will not join orders if they contain the product, but are from another customer. And that is the point of interest now, if that has no joined row, it's not contributing to COUNT(Ord.ID) in contrast to Count(*), which also would count that, COUNT(Ord.ID) is like SUM(IIF(ISNULL(Ord.ID),0,1)), it only counts, if Ord.ID is NOT null. That means HAVING COUNT(Ord.ID)=0 ensures the customer didn't order that product in any of his orders, as Ord.Id is null in orders of other accounts, as they don't fulfill the join condition.

So in short with this join combination COUNT(Ord.ID) counts the number of orders having a certain combination of account (in itself) and product (in its items). With this low amount of test data you can remove the WHERE and see all combinations of productID and AccountIDs not occurring at all, so NO sells of these products were made to these accounts.

In the end, it's counting orders of accounts containing a certain product - if that count is 0, it wasn't sold to that account.

Your situation differs and is easier, with just three tables, but it's unclear how the tables are related, if you please provide that, I'm confident to accustom this query to your situation, it should rather get simpler.

Bye, Olaf.