?
Solved

Sql query conundrum

Posted on 2010-01-08
9
Medium Priority
?
261 Views
Last Modified: 2012-05-08
I have a query, and it retrieves data, but what I need is the OPPOSITE of what this query returns, but I can't get my head around it.

Please let me know if you need more info, and thanks for looking.
SELECT     tblChargeInfo.*
FROM         tblChargeInfo INNER JOIN
                      tblReportData ON tblChargeInfo.ProcCode = tblReportData.ProcCode AND tblChargeInfo.DOS = tblReportData.DOS AND 
                      tblChargeInfo.ClaimNumber = tblReportData.ClaimNumber
WHERE     (tblChargeInfo.OpenAmount = 0) AND (tblChargeInfo.Worked = 1)

Open in new window

0
Comment
Question by:jcroson
9 Comments
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 26213060
Hmm.. it would be easier if you describe what you want to get back from the database.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26213178
Do you mean like this?
....
WHERE     (tblChargeInfo.OpenAmount <> 0) AND (tblChargeInfo.Worked <> 1)
 
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26213194
SELECT     tblChargeInfo.*
FROM         tblChargeInfo
WHERE NOT EXISTS (SELECT 1 FROM
                      tblReportData WHERE tblChargeInfo.ProcCode = tblReportData.ProcCode AND tblChargeInfo.DOS = tblReportData.DOS AND
                      tblChargeInfo.ClaimNumber = tblReportData.ClaimNumber
)
AND     (tblChargeInfo.OpenAmount = 0) AND (tblChargeInfo.Worked = 1)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:jcroson
ID: 26213268
Yesterday I ran an UPDATE query on tblChargeInfo, setting anything that had an OpenAmount = 0 AND Worked = 0 to Worked = 1.

Unfortunately, I neglected to update the table tblReportData we use to track changes made to tblChargeInfo, and need to find these 10k records.

The only thing that makes these records unique is that they have no related data in tblReportData, namely the three INNER JOIN matches I used above to find records that meet the OpenAmount = 0 AND the now UPDATEd Worked = 1.

I thought of creating some VB code that would query tblChargeInfo using OpenAmount = 0 AND Worked = 1, step through the returned recordsets, pulling the three JOIN locations in as variables and querying tblReportData to see if a RecordSet is returned, and if not, update tblReportData.

Then I figured someone out there familiar with sql could probably perform this operation in sql.

0
 
LVL 41

Expert Comment

by:Sharath
ID: 26213290
Do you mean this? Better post sample data and what exactly you want.

SELECT     tblChargeInfo.*
FROM         tblChargeInfo INNER JOIN
                      tblReportData ON tblChargeInfo.ProcCode = tblReportData.ProcCode AND tblChargeInfo.DOS = tblReportData.DOS AND
                      tblChargeInfo.ClaimNumber = tblReportData.ClaimNumber
WHERE     (tblChargeInfo.OpenAmount <> 0) OR (tblChargeInfo.Worked <> 1)
0
 

Author Comment

by:jcroson
ID: 26213330
Actually, I think aneeshattingal has hit the nail on the head.

This query, as I see it, nests a join to negate my match before.

Right?
0
 

Author Comment

by:jcroson
ID: 26213374
So how could I use this query to update tblReportData thusly:

tblReportData.Worked = tblChargeInfo.Worked
tblReportData.Notes = tblChargeInfo.Notes
tblReportData.ClaimNumber = tblChargeInfo.ClaimNumber
tblReportData.ProcCode = tblChargeInfo.ProcCode
tblReportData.DOS = tblChargeInfo.DOS
tblReportData.UpdateDate = Now()

TIA!
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26213401
So you want to find data that is in tblChargeInfo but is not in tblReportData?
You could do it like this as well:

SELECT     tblChargeInfo.* 
FROM         tblChargeInfo 
left JOIN tblReportData ON 	tblChargeInfo.ProcCode = tblReportData.ProcCode AND 
				tblChargeInfo.DOS = tblReportData.DOS AND  
                      		tblChargeInfo.ClaimNumber = tblReportData.ClaimNumber 
WHERE     (tblChargeInfo.OpenAmount = 0) AND (tblChargeInfo.Worked = 1) and
	tblReportDate.ProcCode is null

Open in new window

0
 

Author Comment

by:jcroson
ID: 26273159
In addition to the need to find the data pointed out in the solution, I needed to update another table. I found it easier to use the attached VB code for this task.
Option Explicit

Private Sub Form_Load()
   Dim cnnCharge As ADODB.Connection
   Dim rsCharge As ADODB.Recordset
   Dim rsReport As ADODB.Recordset
   Dim i As Integer
   Dim strNotes As String, strClaimNumber As String, strProcCode As String, strDOS As String, strUpdateDate As String
   
   
   Set cnnCharge = New ADODB.Connection
   Set rsCharge = New ADODB.Recordset
   Set rsReport = New ADODB.Recordset
   
   Dim strChargeSql As String
   
    strChargeSql = "SELECT tblChargeInfo.Notes, tblChargeInfo.ClaimNumber, tblChargeInfo.ProcCode, tblChargeInfo.DOS " & _
              "FROM tblChargeInfo " & _
              "WHERE NOT EXISTS (SELECT 1 FROM tblReportData " & _
              "WHERE tblChargeInfo.ProcCode = tblReportData.ProcCode AND tblChargeInfo.DOS = tblReportData.DOS AND " & _
                      "tblChargeInfo.ClaimNumber = tblReportData.ClaimNumber) " & _
                      "AND (tblChargeInfo.OpenAmount = 0) AND (tblChargeInfo.Worked = 1)"

   ' Open a connection by referencing the ODBC driver.
   cnnCharge.ConnectionString = "driver={SQL Server};" & _
      "server=IBSFP;uid=sqluid;pwd=sqlpwd;database=db120ar"
   cnnCharge.Open
   
   rsCharge.Open strChargeSql, cnnCharge

Do While Not rsCharge.EOF

    strNotes = rsCharge.Fields.Item(0)
    strClaimNumber = rsCharge.Fields.Item(1)
    strProcCode = rsCharge.Fields.Item(2)
    strDOS = rsCharge.Fields.Item(3)
    strUpdateDate = Format(Now + 10, "m/d/yyyy")
    
    rsReport.Open "INSERT INTO tblReportData (Worked, Notes, ClaimNumber, ProcCode, DOS, UpdateDate) " & _
            "VALUES (1, '" & strNotes & "', '" & strClaimNumber & "', '" & strProcCode & "', '" & strDOS & "', '" & Format(Now + 20, "m/d/yyyy") & "')", cnnCharge
    rsCharge.MoveNext
Loop

   ' Close the connection.
   cnnCharge.Close
   
MsgBox "All done."
End Sub

Open in new window

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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