Learn how to a build a cloud-first strategyRegister Now

  • 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.
  • 2
1 Solution
your query looks fine
what do you mean by - it does not work?
marcozamperlinAuthor Commented:

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.
Scott PletcherSenior DBACommented:
NOT EXISTS should be more efficient in this case:

SELECT tblMaster.CodArt
FROM tblMaster m0
    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)
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.

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