<div>
Is this an homework or exam related question?
</div>


<div>
Nor this nor that.<br />
<br />
I am a software developer and I have this problem that I am not being able to solve.<br />
<br />
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 &quot;kill&quot; the task.<br />
<br />
Thanks
</div>


<div>
Try..<br />
<br />
For question 1..<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-41817744-1"><span>SELECT * FROM Customers c</span>
<span>FULL OUTER JOIN Transactions t ON c.CustomerCode = t.CustomerCode</span>
<span>WHERE t.customer IS NULL</span>
</code></pre>
<br />
<br />
<br />
For question 2<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-41817744-2"><span>SELECT I.Itemcode , I.Description,c.customerId FROM Customers c</span>
<span>OUTER APPLY</span>
<span>(</span>
<span>	SELECT I.Itemcode , I.Description , t.Transactions FROM Transactions t</span>
<span>	INNER JOIN Items I on t.Itemcode = I.Itemcode</span>
<span>	WHERE c.CustomerCode = t.CustomerCode</span>
<span>)p</span>
<span>WHERE t.customer IS NULL</span>
</code></pre>
</div>


<div>
Pawan Kumar Khowal<br />
thank You for your feedback.<br />
<br />
Yet in the first select statement, I fail to see the itemcode ...<br />
The thing is that the form I am trying to design, will ask for an itemcode , and when you hit &quot;Continue&quot; it should show you the customers that did not take from that item.<br />
In the first statement, I did not see any reference to an item !<br />
Thanks
</div>


<div>
These are the kind of questions I had when learning databases. That's why I asked.<br />
<br />
You have some options but I like the NOT EXISTS:<br />

<pre><code class="code-10 nolinks" id="code-20-41817753-1"><span>-- Question 1</span>
<span>SELECT Customercode, CustomerName, Salesrep</span>
<span>FROM Customers</span>
<span>WHERE NOT EXISTS (SELECT 1 </span>
<span>        FROM Transactions </span>
<span>        WHERE Transactions.CustomerCode =  Customers.Customercode</span>
<span>             AND Itemcode = @ItemCodeToSearch)</span>
<span></span>
<span>-- Question 2</span>
<span>SELECT Customers.Customercode, Customers.CustomerName, Items.Itemcode, Items.Description </span>
<span>FROM Customers</span>
<span>    INNER JOIN Transactions ON Transactions.CustomerCode =  Customers.Customercode</span>
<span>    INNER JOIN Items ON Items.Itemcode = Transactions.Itemcode </span>
<span>WHERE NOT EXISTS (SELECT 1 </span>
<span>        FROM Transactions T2</span>
<span>        WHERE T2.CustomerCode =  Customers.Customercode</span>
<span>             AND T2.Itemcode = Items.Itemcode)</span>
</code></pre>
NOTE: I didn't test the code so please check if returns what you need.
</div>


<div>
try this ..<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-41817754-1"><span>DECLARE @ItemCode AS VARCHAR(10) = 'abc'</span>
<span></span>
<span>SELECT I.Itemcode , I.Description, c.customerId FROM Customers c</span>
<span>OUTER APPLY</span>
<span>(</span>
<span>	SELECT I.Itemcode , I.Description , t.Transactions FROM Transactions t</span>
<span>	INNER JOIN Items I on t.Itemcode = I.Itemcode</span>
<span>	WHERE c.CustomerCode = t.CustomerCode</span>
<span>	AND t.Itemcode = @ItemCode</span>
<span>)p</span>
<span>WHERE t.customer IS NULL</span>
</code></pre>
</div>


<div>
Is the second query I posted earlier is fine for you ?
</div>


<div>
Pawan Kumar Khowal<br />
<br />
Still checking it.... :)
</div>


<div>
Vitor Montalvão<br />
<br />
Thank you &nbsp;. The first part is covered by your answer.<br />
<br />
Yet the second one, gives me an error (error correlating fields)<br />
<br />
Thanks
</div>


<div>
Please post the error since I can't test it.
</div>


Screenshot--5-.png

<div>
You missed the alias in the subselect:<br />
WHERE NOT EXISTS (SELECT 1 <br />
&nbsp; &nbsp; &nbsp; &nbsp; FROM Transactions <b>T2</b><br />
&nbsp; &nbsp; &nbsp; &nbsp; WHERE <b>T2.</b>CustomerCode = &nbsp;Customers.Customercode<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND <b>T2.</b>Itemcode = Items.Itemcode)
</div>


<div>
Dear Vitor,<br />
same error !<a href="https://filedb.experts-exchange.com/incoming/2016/09_w40/1119080/Screenshot--8-.png" target="_blank" title="Screenshot--8-.png (75 KB)"><img alt="Screenshot--8-.png" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2016/09_w40/800_1119080/Screenshot--8-.png" /></a>
</div>


Screenshot--8-.png

<div>
@Author - Can you try mine if possible ?
</div>


<div>
Pawan Kumar Khowal<br />
<br />
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 !<br />
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 !<br />
<br />
Thanks again!
</div>


<div>
Okies,<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-41817825-1"><span>SELECT I.Itemcode , I.Description,c.customerId FROM Customers c</span>
<span>FULL OUTER JOIN </span>
<span>(</span>
<span>	SELECT I.Itemcode , I.Description , t.Transactions FROM Transactions t</span>
<span>	INNER JOIN Items I on t.Itemcode = I.Itemcode	</span>
<span>)p</span>
<span>ON c.CustomerCode = p.CustomerCode</span>
<span>WHERE p.customer IS NULL</span>
</code></pre>
</div>


<div>
You're using VFP and this may have impact on the query. It should work perfectly in SQL Server.<br />
Lets try to make a small modification:<br />

<pre><code class="code-1 nolinks" id="code-20-41817837-1"><span>SELECT Accounts.Account, Accounts.AccName, Items.Itemcode, Items.Description </span>
<span>FROM Accounts</span>
<span>    INNER JOIN Transtok </span>
<span>        INNER JOIN Items ON Items.Itemcode = Transtok.Itemcode </span>
<span>    ON Transtok.Customer =  Accounts.Account</span>
<span>WHERE NOT EXISTS (SELECT 1 </span>
<span>        FROM Transtok T2</span>
<span>        WHERE T2.Customer =  Accounts.Account</span>
<span>             AND T2.Itemcode = Items.Itemcode)</span>
</code></pre>
</div>


<div>
Vitor Montalvão<br />
<br />
Same Error ! error correlating fields..
</div>


<div>
Check for a tool that will convert this tsql to VFP.. May be tool available online.
</div>


<div>
I guess VFP doesn't accept very well the JOIN so here's an old school solution:<br />

<pre><code class="code-1 nolinks" id="code-20-41817861-1"><span>SELECT Accounts.Account, Accounts.AccName, Items.Itemcode, Items.Description </span>
<span>FROM Accounts, Transtok,Items </span>
<span>WHERE Items.Itemcode = Transtok.Itemcode </span>
<span>    AND Transtok.Customer =  Accounts.Account</span>
<span>    AND NOT EXISTS (SELECT 1 </span>
<span>        FROM Transtok T2</span>
<span>        WHERE T2.Customer =  Accounts.Account AND T2.Itemcode = Items.Itemcode)</span>
</code></pre>
</div>


<div>
Vitor Montalvão<br />
<br />
Sorry,<br />
but same problem again !
</div>


<div>
Only have a phone but this might help q1<br />
<br />
Select c.*<br />
From customers c<br />
Left join transactions t on c.custno = t.custno<br />
Where t.itemno IS NULL
</div>


<div>
<blockquote>
I m using vfp 9 
</blockquote>
I guess is the last version, right?<br />
<br />
Did you try my last query?
</div>


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


<div>
Ok I need some data mockup.<br />
Can you post non real data sample so I can play with it to verify what's missing?
</div>


<div>
OK. &nbsp;I would need few min.<br />
Thanks
</div>


<div>
Excel or CSV will be good so I can import it easily.
</div>


<div>
Vitor Montalvão,<br />
<br />
Thank you very much for your efforts.<br />
<br />
I took the first SQL Statement and tweaked it a little (put it in a SCAN function) and got what I needed.<br />
<br />
Thank you very very much.<br />
<br />
Regards
</div>


<div>
Why is a suggested query using outer apply that could not be used chosen as an assisted answer?<br />
<br />
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.
</div>


<div>
PortletPaul<br />
<br />
The outer Apply syntax did not work with me and therefore I did not consider it as part of the solution.<br />
As I said it is my mistake that I did not specify that it was for a VFP application and not a &quot;pure&quot; SQL hence the impossibility for me to test the outer apply syntax.<br />
<br />
I hope I could clear the matter and once again I apologize for not being accurate in the question.
</div>


<div>
Pierre, can you close this question properly?<br />
Cheers.
</div>


<div>
I don't remember anymore which comments were marked as solution and looks like Pierre didn't return to close this properly.
</div>


<div>
previously<br />
ID: 41817753 350 points<br />
ID: 41817754 150 points<br />
<br />
but that was incorrect
</div>


<div>
<div class="content wysiwyg-content">
I have 3 tables.<br />
Table1 : Items - Itemcode - Description - Brand - <br />
Table 2 : Customers : Customercode - CustomerName - Salesrep<br />
Table 3 :Transactions : Date - Reference-CustomerCode-Sal<wbr />esrep-Item<wbr />code - Qty-Value-<br />
<br />
my question is as follows:<br />
<br />
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)<br />
<br />
The second question will be how can them all ...<br />
i.e. each customer what are the items that they did not buy (no transaction)<br />
<br />
Thank you
</div>
</div>


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

SQL Statement How to get the &quot;non-data&quot; ?

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

SQL Statement How to get the &quot;non-data&quot; ?

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