Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

How to find unused items

I have a main table (tblMaster) that contains the inventory items. I need to know which have never been used. I have other 4 tables (tblMov1, tblMov2 ...) that contain various types of movements. I thought of using the right join progressive.

I use Visual Basic 6 and ADODB connection.
I have write this code:

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
   
    Dim tblMaster As ADODB.Recordset
    Dim tblMov1 As ADODB.Recordset
    Dim tblMov2 As ADODB.Recordset
    Dim tblMov3 As ADODB.Recordset
    Dim tblMov4 As ADODB.Recordset
   
    Dim TblNotUsed As ADODB.Recordset
       
    conn.Open "DSN_ABACUS", "sa", "EdisoftwareSa09"
   
    Set tblMaster = New ADODB.Recordset
    Set tblMov1 = New ADODB.Recordset
    Set tblMov2 = New ADODB.Recordset
    Set tblMov3 = New ADODB.Recordset
    Set tblMov4 = New ADODB.Recordset
    Set TblNotUsed = New ADODB.Recordset

    TblNotUsed.CursorLocation = adUseClient
   
    TblNotUsed.Open "SELECT tblMaster.CodArt FROM tblMov1 RIGHT JOIN tblMaster ON tblMov1.CodArt=tblMaster.CodArt WHERE tblMov1.CodArt Is Null", conn, adOpenDynamic, adLockBatchOptimistic
   
    TblNotUsed.Open "SELECT TblNotUsed.CodArt FROM tblMov2 RIGHT JOIN TblNotUsed ON tblMov2.CodArt=TblNotUsed.CodArt WHERE tblMov2.CodArt Is Null", conn, adOpenDynamic, adLockBatchOptimistic




but does not work ...

thanks in advance.
Marco
0
marcozamperlin
Asked:
marcozamperlin
  • 2
1 Solution
 
momi_sabagCommented:
your query looks fine
what do you mean by - it does not work?
0
 
marcozamperlinAuthor Commented:
@momi_sabag:

The second query should be based on the results of the first. And the third (that I have not reported) of the second and so on ... So that the end will know what are the items that have not been handled in the 4 tables tblMovx.
0
 
Scott PletcherSenior DBACommented:
NOT EXISTS should be more efficient in this case:


SELECT tblMaster.CodArt
FROM tblMaster m0
WHERE
    NOT EXISTS (SELECT 1 FROM dbo.tblMov1 m1 WHERE m1.CodArt = m0.CodArt) AND
    NOT EXISTS (SELECT 1 FROM dbo.tblMov2 m2 WHERE m2.CodArt = m0.CodArt) AND
    NOT EXISTS (SELECT 1 FROM dbo.tblMov3 m3 WHERE m3.CodArt = m0.CodArt) AND
    NOT EXISTS (SELECT 1 FROM dbo.tblMov4 m4 WHERE m4.CodArt = m0.CodArt)
0
 
Scott PletcherSenior DBACommented:
OOPS, just noticed, you may need to change "AND"s to "OR"s, depending on what you are trying to do.

But the general principal is still the same.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now