Results 1 to 12 of 12

Thread: [RESOLVED] Need help with this complicated query

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    586

    Resolved [RESOLVED] Need help with this complicated query

    Hello vbforums
    I have 3 tables as shown below
    I want to delete all records that are in both tbl1 and tbl3 but do not exist in tbl2

    Name:  query3.jpg
Views: 84
Size:  43.1 KB

    this is what I'm doing but no success.

    Code:
    StrSql = "Select * From Tbl1 inner join Tbl2 on  Tbl1.Id = Tbl2.PID " & _
    " inner join Tbl3 on Tbl1.ID = Tbl3.SID" & _
    "  where pass =  'Non' and PID <> SID "
    
     Set Rs = Cnn.OpenRecordset(StrSql)
    
     If Not Rs.EOF Then
     Do While Not Rs.EOF
        Rs.Delete
     Rs.MoveNext
            Loop
    End If
    End If
    In this scenarion I expect this query to delete Record 4 in Tbl1 and Tbl3.
    thank you

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,819

    Re: Need help with this complicated query

    This?

    Code:
    select * from T1 inner Join T3 on T1.id = T3.sid where T1.id not in (select T2.pid from T2)
    .

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    586

    Re: Need help with this complicated query

    Quote Originally Posted by sapator View Post
    This?

    Code:
    select * from T1 inner Join T3 on T1.id = T3.sid where T1.id not in (select T2.pid from T2)
    thank you very much
    It worked to sort out the record
    But it fails to delete.

    Code:
    If Not Rs.EOF Then
     Do While Not Rs.EOF
        Rs.Delete
     Rs.MoveNext
            Loop
    End If

  4. #4
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,703

    Re: Need help with this complicated query

    Perhaps this will do the trick?

    Code:
    Cnn.Execute "DELETE FROM Tbl1 WHERE ID IN (SELECT ID FROM Tbl1 INNER JOIN Tbl3 ON Tbl1.ID = Tbl3.SID WHERE Tbl1.ID NOT IN (SELECT Tbl2.PID FROM Tbl2))"

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,955

    Re: Need help with this complicated query

    Quote Originally Posted by jpbro View Post
    Perhaps this will do the trick?

    Code:
    Cnn.Execute "DELETE FROM Tbl1 WHERE ID IN (SELECT ID FROM Tbl1 INNER JOIN Tbl3 ON Tbl1.ID = Tbl3.SID WHERE Tbl1.ID NOT IN (SELECT Tbl2.PID FROM Tbl2))"
    I had that exact same post and then deleted it because it appears he wants to delete one at a time from the client (is that ADO?)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    586

    Re: Need help with this complicated query

    Quote Originally Posted by jpbro View Post
    Perhaps this will do the trick?

    Code:
    Cnn.Execute "DELETE FROM Tbl1 WHERE ID IN (SELECT ID FROM Tbl1 INNER JOIN Tbl3 ON Tbl1.ID = Tbl3.SID WHERE Tbl1.ID NOT IN (SELECT Tbl2.PID FROM Tbl2))"
    Big progress but still I can't find my happiness because I can only delete from one table0
    thank you

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    586

    Re: Need help with this complicated query

    Quote Originally Posted by szlamany View Post
    (is that ADO?)
    Sqlite and RC6 but The same approach as ADO.

  8. #8
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,703

    Re: Need help with this complicated query

    I don't think there's any advantage to deleting records in a loop vs. via an SQL statement. However, if you do want to use a loop for some reason, I think you might need to travel in reverse. E.g.:

    Code:
    If Rs.Recordcount Then
       Rs.MoveLast
    
       Do Until Rs.BOF
          Rs.Delete
    
          Rs.MovePrevious
       Loop
    End If

  9. #9
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    11,061

    Re: Need help with this complicated query

    Perhaps you could do it like this, delete the records from the 2 tables while traversing the records. Hand-coded, please check for any syntax error. Another alternative is you could CASCADE the delete from tbl1 to tbl3 such that if one record is deleted from tbl1 then it's corresponding record from tbl3 is also deleted. I am just not sure if this is possible with sqlite as I haven't used it.

    Code:
    If Not Rs.EOF Then
     Do While Not Rs.EOF
        'Rs.Delete
        Cnn.Execute "DELETE FROM Tbl1 WHERE ID = " & rs.fields("ID") & ""
        Cnn.Execute "DELETE FROM Tbl3 WHERE ID = " & rs.fields("ID") & ""
     Rs.MoveNext
            Loop
    End If
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    586

    Re: Need help with this complicated query

    Thank you all
    I think I found a workaround
    Code:
    Cnn.Execute "DELETE FROM Tbl1 WHERE ID IN (SELECT ID FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.ID = Tbl2.PID " & _
     " WHERE Tbl1.ID NOT IN (SELECT Tbl3.SID FROM Tbl3))"
    
     Cnn.Execute "DELETE FROM Tbl2 " & _
     " WHERE Tbl2.PID NOT IN (SELECT Tbl1.ID FROM Tbl1)"
    But I have a question out of curiosity.
    This code deletes from Tbl1 first then Tbl2.
    If I try to do that manually, I get message error, foreing key constraint.
    To be able to delete a record manually, I must delete first from Tbl2.
    thank you

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,343

    Re: Need help with this complicated query

    Yes, if you have FK-Constraints, you have to delete in reverse Order, since the constraint won't allow a child-record being without a parent.

    The more interesting question: What kind of constraints do you have on your Foreign Key?
    If you have a DELETE ON CASCADE, then you just need to delete the parent-record, and the DBMS takes care of deleting the child.
    No need to delete from the child-table (that's probably, why your "manual" delete fails)
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    586

    Re: [RESOLVED] Need help with this complicated query

    Code:
    If you have a DELETE ON CASCADE, then you just need to delete the parent-record, and the DBMS takes care of deleting the child.
    that's it thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width