Solved

SQL Statement How to get the "non-data" ?

Posted on 2016-09-27
38
68 Views
Last Modified: 2016-10-30
I have 3 tables.
Table1 : Items - Itemcode - Description - Brand -
Table 2 : Customers : Customercode - CustomerName - Salesrep
Table 3 :Transactions : Date - Reference-CustomerCode-Salesrep-Itemcode - Qty-Value-

my question is as follows:

I need to know what customers did NOT buy from that item- (i.e. there is no record in Transactions that has this customer and this item)

The second question will be how can them all ...
i.e. each customer what are the items that they did not buy (no transaction)

Thank you
0
Comment
Question by:Pierre Ammoun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 12
  • 6
  • +1
38 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41817734
Is this an homework or exam related question?
0
 

Author Comment

by:Pierre Ammoun
ID: 41817737
Nor this nor that.

I am a software developer and I have this problem that I am not being able to solve.

A brute-force solution was to do a dual -scan (scan the items and for each item scan the transactions table) but I hit a rock when the records were too much and it would take a huge amount of time that most of the time I have to "kill" the task.

Thanks
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41817744
Try..

For question 1..

SELECT * FROM Customers c
FULL OUTER JOIN Transactions t ON c.CustomerCode = t.CustomerCode
WHERE t.customer IS NULL

Open in new window




For question 2

SELECT I.Itemcode , I.Description,c.customerId FROM Customers c
OUTER APPLY
(
	SELECT I.Itemcode , I.Description , t.Transactions FROM Transactions t
	INNER JOIN Items I on t.Itemcode = I.Itemcode
	WHERE c.CustomerCode = t.CustomerCode
)p
WHERE t.customer IS NULL

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Pierre Ammoun
ID: 41817749
Pawan Kumar Khowal
thank You for your feedback.

Yet in the first select statement, I fail to see the itemcode ...
The thing is that the form I am trying to design, will ask for an itemcode , and when you hit "Continue" it should show you the customers that did not take from that item.
In the first statement, I did not see any reference to an item !
Thanks
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41817753
These are the kind of questions I had when learning databases. That's why I asked.

You have some options but I like the NOT EXISTS:
-- Question 1
SELECT Customercode, CustomerName, Salesrep
FROM Customers
WHERE NOT EXISTS (SELECT 1 
        FROM Transactions 
        WHERE Transactions.CustomerCode =  Customers.Customercode
             AND Itemcode = @ItemCodeToSearch)

-- Question 2
SELECT Customers.Customercode, Customers.CustomerName, Items.Itemcode, Items.Description 
FROM Customers
    INNER JOIN Transactions ON Transactions.CustomerCode =  Customers.Customercode
    INNER JOIN Items ON Items.Itemcode = Transactions.Itemcode 
WHERE NOT EXISTS (SELECT 1 
        FROM Transactions T2
        WHERE T2.CustomerCode =  Customers.Customercode
             AND T2.Itemcode = Items.Itemcode)

Open in new window

NOTE: I didn't test the code so please check if returns what you need.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41817754
try this ..

DECLARE @ItemCode AS VARCHAR(10) = 'abc'

SELECT I.Itemcode , I.Description, c.customerId FROM Customers c
OUTER APPLY
(
	SELECT I.Itemcode , I.Description , t.Transactions FROM Transactions t
	INNER JOIN Items I on t.Itemcode = I.Itemcode
	WHERE c.CustomerCode = t.CustomerCode
	AND t.Itemcode = @ItemCode
)p
WHERE t.customer IS NULL

Open in new window

0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41817758
Is the second query I posted earlier is fine for you ?
0
 

Author Comment

by:Pierre Ammoun
ID: 41817761
Pawan Kumar Khowal

Still checking it.... :)
0
 

Author Comment

by:Pierre Ammoun
ID: 41817770
Vitor Montalvão

Thank you  . The first part is covered by your answer.

Yet the second one, gives me an error (error correlating fields)

Thanks
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41817773
Please post the error since I can't test it.
0
 

Author Comment

by:Pierre Ammoun
ID: 41817782
Screenshot--5-.png
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41817791
You missed the alias in the subselect:
WHERE NOT EXISTS (SELECT 1
        FROM Transactions T2
        WHERE T2.CustomerCode =  Customers.Customercode
             AND T2.Itemcode = Items.Itemcode)
0
 

Author Comment

by:Pierre Ammoun
ID: 41817804
Dear Vitor,
same error !Screenshot--8-.png
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41817807
@Author - Can you try mine if possible ?
0
 

Author Comment

by:Pierre Ammoun
ID: 41817815
Pawan Kumar Khowal

Thank you once again but my problem with your second answer is that in VFP (my programming language) I do not have the Apply Command in SQL Statement !
I think I made a mistake by saying that I was looking for a SQL Statment... I thought that VFP works the same way as SQL . Seems that there are certain commands not applicable !

Thanks again!
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41817825
Okies,

SELECT I.Itemcode , I.Description,c.customerId FROM Customers c
FULL OUTER JOIN 
(
	SELECT I.Itemcode , I.Description , t.Transactions FROM Transactions t
	INNER JOIN Items I on t.Itemcode = I.Itemcode	
)p
ON c.CustomerCode = p.CustomerCode
WHERE p.customer IS NULL

Open in new window

0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41817837
You're using VFP and this may have impact on the query. It should work perfectly in SQL Server.
Lets try to make a small modification:
SELECT Accounts.Account, Accounts.AccName, Items.Itemcode, Items.Description 
FROM Accounts
    INNER JOIN Transtok 
        INNER JOIN Items ON Items.Itemcode = Transtok.Itemcode 
    ON Transtok.Customer =  Accounts.Account
WHERE NOT EXISTS (SELECT 1 
        FROM Transtok T2
        WHERE T2.Customer =  Accounts.Account
             AND T2.Itemcode = Items.Itemcode)

Open in new window

0
 

Author Comment

by:Pierre Ammoun
ID: 41817852
Vitor Montalvão

Same Error ! error correlating fields..
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41817859
Check for a tool that will convert this tsql to VFP.. May be tool available online.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41817861
I guess VFP doesn't accept very well the JOIN so here's an old school solution:
SELECT Accounts.Account, Accounts.AccName, Items.Itemcode, Items.Description 
FROM Accounts, Transtok,Items 
WHERE Items.Itemcode = Transtok.Itemcode 
    AND Transtok.Customer =  Accounts.Account
    AND NOT EXISTS (SELECT 1 
        FROM Transtok T2
        WHERE T2.Customer =  Accounts.Account AND T2.Itemcode = Items.Itemcode)

Open in new window

0
 

Author Comment

by:Pierre Ammoun
ID: 41817869
Vitor Montalvão

Sorry,
but same problem again !
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41817891
Only have a phone but this might help q1

Select c.*
From customers c
Left join transactions t on c.custno = t.custno
Where t.itemno IS NULL
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41817899
What version of VFP are you using? Looks like VFP is very weak for SQL commands.
One last try, please:
SELECT Accounts.Account, Accounts.AccName, Items.Itemcode, Items.Description 
FROM Accounts
    INNER JOIN Transtok ON Transtok.Customer =  Accounts.Account
    INNER JOIN Items ON Items.Itemcode = Transtok.Itemcode 
    LEFT JOIN Transtok T2 ON T2.Customer =  Accounts.Account AND T2.Itemcode = Items.Itemcode
WHERE T2.Itemcode IS NULL

Open in new window

0
 

Author Comment

by:Pierre Ammoun
ID: 41817923
I m using vfp 9
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41817928
I m using vfp 9
I guess is the last version, right?

Did you try my last query?
0
 

Author Comment

by:Pierre Ammoun
ID: 41817935
Vitor Montalvão


We are getting somewhere ! Now no more error, but this time the Query returns an empty cursor.
Which is not possible. I know for a fact that there are many items not linked to any transactions (per customer)

Thanks
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41817941
Ok I need some data mockup.
Can you post non real data sample so I can play with it to verify what's missing?
0
 

Author Comment

by:Pierre Ammoun
ID: 41817949
OK.  I would need few min.
Thanks
0
 

Author Comment

by:Pierre Ammoun
ID: 41817952
Which format ?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41817955
Excel or CSV will be good so I can import it easily.
0
 

Author Comment

by:Pierre Ammoun
ID: 41818064
Vitor Montalvão,

Thank you very much for your efforts.

I took the first SQL Statement and tweaked it a little (put it in a SCAN function) and got what I needed.

Thank you very very much.

Regards
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41818816
Why is a suggested query using outer apply that could not be used chosen as an assisted answer?

Points should not be allocated for mere participation or volune of participation. The enduring value of the question/answer database is reduced if that happens.
0
 

Author Comment

by:Pierre Ammoun
ID: 41819088
PortletPaul

The outer Apply syntax did not work with me and therefore I did not consider it as part of the solution.
As I said it is my mistake that I did not specify that it was for a VFP application and not a "pure" SQL hence the impossibility for me to test the outer apply syntax.

I hope I could clear the matter and once again I apologize for not being accurate in the question.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41839583
Pierre, can you close this question properly?
Cheers.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41859900
I don't remember anymore which comments were marked as solution and looks like Pierre didn't return to close this properly.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41859935
previously
ID: 41817753 350 points
ID: 41817754 150 points

but that was incorrect
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question