Results 1 to 21 of 21

Thread: Need help with this query

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    689

    Need help with this query

    Hello everyone
    First I apologize for posting here. In fact I never posted elsewhere.
    Please moderators keep my post here
    This query is driving me mad
    This is approxamately my database:
    Name:  pic.jpg
Views: 124
Size:  19.4 KB
    In my qery; I'm targeting the record in red.
    For this I se:
    Code:
    StrSql = "Select * from Item_Tbl " & _
    " inner join Tbl_Debt  on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
    " inner join Tbl_detail  on Item_Tbl. ID = Tbl_detail. Item.Id " & _
    " where Tbl_Debt.PID = 2  "
    So I expect to get this:
    Name:  pic125.png
Views: 119
Size:  1.9 KB
    But I'm having this:
    Name:  pic5.png
Views: 120
Size:  2.2 KB
    thank you all
    Last edited by newbie2; Feb 8th, 2021 at 06:47 AM.

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

    Re: Need help with this query

    You have ID of 2 in your Item_Tbl table that is colored RED but you color the row with Item_Id of "1" in you Tbl_Debt.

    You requirement is flawed or not drawn properly.

    Are these tables of your own design and are you stuck with these names?

    *** 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

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

    Re: Need help with this query

    He only compares Item_ID
    If he compares PID too, then he will get his result
    Code:
    StrSql = "Select * from Item_Tbl " & _
    " inner join Tbl_Debt  on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
    " inner join Tbl_detail  on Item_Tbl. ID = Tbl_detail. Item.Id and Tbl_Debt. PID = Tbl_detail. PID " & _
    " where Tbl_Debt.PID = 2  "
    Last edited by Zvoni; Feb 8th, 2021 at 07:52 AM.
    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

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    689

    Re: Need help with this query

    Quote Originally Posted by szlamany View Post
    You have ID of 2 in your Item_Tbl table that is colored RED but you color the row with Item_Id of "1" in you Tbl_Debt.

    You requirement is flawed or not drawn properly.

    Are these tables of your own design and are you stuck with these names?
    You're right. I should have colored the first row.
    Attachment 180137
    Name:  pic.jpg
Views: 96
Size:  19.6 KB

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    689

    Re: Need help with this query

    Quote Originally Posted by Zvoni View Post
    He only compares Item_ID
    If he compares PID too, then he will get his result
    Code:
    StrSql = "Select * from Item_Tbl " & _
    " inner join Tbl_Debt  on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
    " inner join Tbl_detail  on Item_Tbl. ID = Tbl_detail. Item.Id and Tbl_Debt. PID = Tbl_detail. PID " & _
    " where Tbl_Debt.PID = 2  "
    Thank you but still having trouble
    In this senario for example:
    Attachment 180139
    Name:  111.jpg
Views: 97
Size:  16.5 KB
    Code:
    StrSql = "Select * from Item_Tbl " & _
    " inner join Tbl_Debt  on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
    " inner join Tbl_detail  on Item_Tbl. ID = Tbl_detail. Item.Id and Tbl_Debt. PID = Tbl_detail. PID " & _
    " where Tbl_Debt.PID = 1  "
    Set Rs = Cnn.OpenRecordset(StrSql)
    MsgBox Rs.RecordCount
    The expected result is 3
    But I'm getting 5
    thank you
    Last edited by newbie2; Feb 8th, 2021 at 11:51 AM.

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    5,368

    Re: Need help with this query

    If something is too complex, break it up into steps.

    In your case, I would define two separate Views:

    1) the first view (vw_Debt_Detail) connects only the two tables:
    .. Tbl_Debt and Tbl_Detail (via PID and Item_ID joining) ... easy to do, easy to verify - useful in your App also in this form

    2) the second view (vw_Debt_Detail_Item) connects your just created view with another table:
    .. vw_Debt_Detail and Item_Tbl (via join on Item_ID = ID)

    HTH

    Olaf

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    689

    Re: Need help with this query

    Quote Originally Posted by Schmidt View Post
    If something is too complex, break it up into steps.


    Olaf
    Thank you Olaf for this idea
    When I use two separate Views, in the senarion above, I'm getting the right result
    Code:
    StrSql = "Select * from Item_Tbl " & _
    " inner join Tbl_Debt  on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
    " where Tbl_Debt.PID = 1  "
    Set Rs = Cnn.OpenRecordset(StrSql)
    MsgBox Rs.RecordCount
    Or
    Code:
    StrSql = "Select * from Item_Tbl " & _
    " inner join Tbl_detail  on Item_Tbl. ID = Tbl_detail. Item.Id  " & _
    " where Tbl_detail.PID = 1  "
    Set Rs = Cnn.OpenRecordset(StrSql)
    MsgBox Rs.RecordCount
    Rs.RecordCount = 3

    However When I use all in one view, I get incorrect result.
    Code:
    StrSql = "Select * from Item_Tbl " & _
    " inner join Tbl_Debt  on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
    " inner join Tbl_detail  on Item_Tbl. ID = Tbl_detail. Item.Id and Tbl_Debt. PID = Tbl_detail. PID " & _
    " where Tbl_Debt.PID = 1  "
    Set Rs = Cnn.OpenRecordset(StrSql)
    MsgBox Rs.RecordCount
    Rs.RecordCount = 5

  8. #8
    PowerPoster
    Join Date
    Jun 2013
    Posts
    5,368

    Re: Need help with this query

    You need to read more carefully - or ask when you do not understand a term (as e.g. View).

    A View stores a certain SQL-QueryString under a given (View-)Name (in the DB, permanently) -
    and that Views Name can then be used, to "callup" the whole SQL-Query, as if it was a "real Table".

    A good analogy is, when you imagine an existing view like a:
    "VariableName" (or a "virtual TableName"), that stands for a complex Select, but isn't a real table"

    Here is the implementation of Step 1) from post #6 above (please read that part again, before you dive into the code here) -
    the resulting RecordCount for the View: vw_Debt_Detail is - as expected 3 - not 5.

    Code:
    Private Cnn As cConnection, Rs As cRecordset
    
    Private Sub Form_Load()
      Set Cnn = New_c.Connection(, DBCreateInMemory)
      
      Cnn.Execute "Create Table  Tbl_Debt(PID Integer, Item_ID Integer, BrandID Integer, Dept Text)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 2)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 2)"
      
      Cnn.Execute "Create Table  Tbl_Detail(PID Integer, Item_ID Integer, BrandID Integer)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 2)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(2, 2)"
        
      With New_c.ArrayList(vbString) 'Step 1) construction (via joined String-ArrayList)
        .Add "Create View vw_Debt_Detail As"
        .Add "Select Tbl_Debt.PID, Tbl_Debt.Item_ID, Tbl_Debt.BrandID, Tbl_Debt.Dept"
        .Add "From Tbl_Debt Inner Join Tbl_Detail"
        .Add "On Tbl_Debt.PID=Tbl_Detail.PID AND Tbl_Debt.Item_ID=Tbl_Detail.Item_ID"
        
        Cnn.Execute .Join(vbCrLf)
      End With
      
      Set Rs = Cnn.OpenRecordset("Select * From vw_Debt_Detail Where PID=1") '<- a view can be used like a table
      Debug.Print "vw_Debt_Detail", Rs.RecordCount, Rs.Fields.Count
    End Sub
    Now you should be able, to implement Step 2) entirely on your own -
    which inner joins our new "table-like" View vw_Debt_Detail - with the normal table Item_Tbl.

    Below is some code, to get you started:
    Code:
      With New_c.ArrayList(vbString) 'Step 2) construction
        .Add "Create View vw_Debt_Detail_Item As"
        '... to be enhanced by you, joining via vw_Debt_Detail.Item_ID and Item_Tbl.ID
        
        Cnn.Execute .Join(vbCrLf)
      End With
    HTH

    Olaf
    Last edited by Schmidt; Feb 8th, 2021 at 04:12 PM.

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,288

    Re: Need help with this query

    Then there must be something wrong with your data...

    Here is a script I just used to replicate your tables and data:
    Code:
    create table Item_Tbl (
        ID INTEGER,
        Item CHAR(10 CHAR)
    );
    create table Tbl_Debt (
        PID INTEGER,
        Item_ID INTEGER,
        Dept INTEGER,
        BrandId INTEGER
    );
    create table Tbl_detail (
        PID INTEGER,
        Item_ID INTEGER,
        BrandID INTEGER
    );
    
    COMMIT;
    
    insert into Item_Tbl (Id, Item) VALUES (1, 'aaa');
    insert into Item_Tbl (Id, Item) VALUES (2, 'bbb');
    insert into Item_Tbl (Id, Item) VALUES (3, 'ccccc');
    insert into Item_Tbl (Id, Item) VALUES (4, 'dddd');
    
    insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (1 ,1, 30, 1);
    insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (1 ,2, 0, 1);
    insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (1 ,2, 0, 1);
    insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (2 ,1, 0, 1);
    insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (3 ,4, 20, 1);
    insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (3 ,3, 0, 1);
    
    insert into Tbl_detail (PID, Item_ID, BrandID) values (1,1,1);
    insert into Tbl_detail (PID, Item_ID, BrandID) values (1,2,1);
    insert into Tbl_detail (PID, Item_ID, BrandID) values (2,1,1);
    insert into Tbl_detail (PID, Item_ID, BrandID) values (3,2,1);
    insert into Tbl_detail (PID, Item_ID, BrandID) values (3,3,1);
    
    COMMIT;
    
    select *
    from item_tbl I
    inner join tbl_debt D on I.ID = D.Item_Id 
    inner join tbl_detail DT on D.Item_Id = DT.Item_Id and D.PID = DT.PID
    where d.pid = 1;
    
    drop table item_tbl;
    drop table tbl_debt;
    drop table tbl_detail;
    commit;
    I got three rows:

    Code:
    1,'aaa       ',1,1,30,1,1,1,1
    2,'bbb       ',1,2,0,1,1,2,1
    2,'bbb       ',1,2,0,1,1,2,1
    So either there's something else in your data, or there's something else in your query, that's off.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    689

    Re: Need help with this query

    Thank you Schmidt
    Thank you techgnome
    I'm sorry for being late in replying to you.
    In fact I spent very long time testing your codes for different scenarios until 2 AM.
    Both the codes give the same results in all the scenarios I tested.
    However I'm still confused.
    In some scenarios, the codes are giving unexpected results which is confusing me.
    So I think I need a kind-hearted man to explain me why this is happening.
    First scenario:

    Code:
    Set Cnn = New_c.Connection(, DBCreateInMemory)
      Cnn.Execute "Create Table  Item_Tbl(ID Integer, Item Text)"
        Cnn.Execute "Insert Into Item_Tbl(ID, Item) Values(1, 'aaa')"
        Cnn.Execute "Insert Into Item_Tbl(ID, Item) Values(2, 'bbb')"
        Cnn.Execute "Insert Into Item_Tbl(ID, Item) Values(3, 'ccc')"
      
      Cnn.Execute "Create Table  Tbl_Debt(PID Integer, Item_ID Integer)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 2)"
      
      Cnn.Execute "Create Table  Tbl_Detail(PID Integer, Item_ID Integer)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 2)"
    
     StrSql = "Select * from Item_Tbl I " & _
    " inner join Tbl_Debt D on I.ID = D.Item_ID " & _
    " inner join Tbl_Detail DT on I.ID  = DT.Item_ID And  D.PID = DT.PID" & _
    " where D.PID = 1 "
    
      Set Rs = Cnn.OpenRecordset(StrSql)
     MsgBox Rs.RecordCount
     Cnn.Execute "drop table item_tbl;"
    Cnn.Execute "drop table tbl_debt;"
    Cnn.Execute "drop table tbl_detail;"
    I expect Rs.RecordCount = 3
    But here it is giving 5
    Even with Schmidt's approach, it is giving 5

    Code:
    vw_Debt_Detail               ....... 5       ....... 4 
    vw_Debt_Detail_Item       .....   5       ......  3
    In the next scenario I become more and more confused.
    Code:
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
      
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
    StrSql = "Select * from Item_Tbl I " & _
    " inner join Tbl_Debt D on I.ID = D.Item_ID " & _
    " inner join Tbl_Detail DT on I.ID  = DT.Item_ID And  D.PID = DT.PID" & _
    " where D.PID = 1 "
    
      Set Rs = Cnn.OpenRecordset(StrSql)
     MsgBox Rs.RecordCount
    Surprisingly the Rs.RecordCount is 9
    And with Olaf's approach the result is:
    Code:
    vw_Debt_Detail         .....      9       ......      4 
    vw_Debt_Detail_Item    .....      9       ....      3
    With this this scenario it is giving 12

    Code:
     Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
      Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
      
      
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
    I'm totally lost.
    Thank you again
    Last edited by newbie2; Feb 9th, 2021 at 06:17 AM.

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

    Re: Need help with this query

    It is really clear to me what is going on. Let's just work with your first example

    Code:
    Create Table  #Item_Tbl(ID Integer, Item Text)
    Insert Into #Item_Tbl(ID, Item) Values(1, 'aaa')
    Insert Into #Item_Tbl(ID, Item) Values(2, 'bbb')
    Insert Into #Item_Tbl(ID, Item) Values(3, 'ccc')
      
    Create Table  #tbl_Debt(PID Integer, Item_ID Integer)
    Insert Into #tbl_Debt(PID, Item_ID) Values(1, 1)
    Insert Into #tbl_Debt(PID, Item_ID) Values(1, 1)
    Insert Into #tbl_Debt(PID, Item_ID) Values(1, 2)
      
    Create Table  #tbl_Detail(PID Integer, Item_ID Integer)
    Insert Into #tbl_Detail(PID, Item_ID) Values(1, 1)
    Insert Into #tbl_Detail(PID, Item_ID) Values(1, 1)
    Insert Into #tbl_Detail(PID, Item_ID) Values(1, 2)
    
    Select * From #Item_Tbl
    Select * From #tbl_Debt
    Select * From #tbl_Detail
    
    Select * from #Item_Tbl I 
     inner join #tbl_Debt D on I.ID = D.Item_ID
     inner join #tbl_Detail DT on I.ID  = DT.Item_ID And  D.PID = DT.PID
     where D.PID = 1 
    
     Drop Table #Item_Tbl
     Drop TAble #tbl_Debt
     Drop Table #tbl_Detail
    You have two rows in Tbl_Debt with both 1 and 1 in both fields.

    Each of these rows will join to the "same two rows" with both 1 and 1 in both fields on the Tbl_Detail table.

    It is this this "weak" join causing 4 rows to appear.

    sidebar: that is bad data - rows with completely duplicated values should NEVER appear in a database. You would have no way to select just one or delete just one. If this is just sample data, then get better sample data, because you are killing your self with these values.

    I've seen novice SQL programmers then put SELECT DISTINCT on the query to fix this! Argh, help me please!
    Last edited by szlamany; Feb 9th, 2021 at 06:35 AM.

    *** 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

  12. #12
    PowerPoster
    Join Date
    Jun 2013
    Posts
    5,368

    Re: Need help with this query

    Can only agree with szlamany...

    It's bad table-design which leads to all this confusion.

    Item_Tbl seens to be Ok, in that it seems to have a real PK (Primary Key) with its ID-Field.
    (not in the short example you've posted, but I assume in your real DB, Item_Tbl.ID was defined as "Integer Primary Key").

    But what about the other two Tables (Tbl_Debt and Tbl_Detail)?

    Could you please answer all of the following questions, because otherwise we cannot help:

    1) What is the PK of Tbl_Debt? (if it's not a single Field, then post the combination of Fields which make up the combined PK)
    2) What is the PK of Tbl_Detail? (if it's not a single Field, then post the combination of Fields which make up the combined PK)
    3) What does the Field PID stand for? (it's obviously an abbreviation, but what is it concretely)
    4) If PID points to "records of a different Table", what is that Tables Name?

    5) it would be nice, when you could post your real Schema-Definitions for:
    --- Item_Tbl
    --- Tbl_Debt
    --- Tbl_Detail
    --- and the Table the PID-Field obviously points to

    As for the last request #5) ... you can easily do that in "Break-Mode" in the Immediate-Window via:
    ?Cnn.Databases("main").Tables("TheTableName").SQLForCreate

    Olaf

  13. #13
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,950

    Re: Need help with this query

    This is clearly a database question, not a programming question. Normally, I would just move it, but in the original post, you asked that the thread not be moved. Why is that?
    My usual boring signature: Nothing

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    689

    Re: Need help with this query

    Item_Tbl seens to be Ok, in that it seems to have a real PK (Primary Key) with its ID-Field.
    (not in the short example you've posted, but I assume in your real DB, Item_Tbl.ID was defined as "Integer Primary Key").
    That's Right sir
    This is the code for creating this table.
    Code:
    Cnn.Execute "Create TABLE  IF NOT EXISTS  Item_Tbl(ID Integer PRIMARY KEY, Item Text)"
    What is the PK of Tbl_Debt? (if it's not a single Field, then post the combination of Fields which make up the combined PK)
    Code:
    FOREIGN KEY(`PID`) REFERENCES `Main_Tbl`(`ID`),
    FOREIGN KEY(`Item_ID`) REFERENCES `Item_Tbl`(`ID`),
    FOREIGN KEY(`brand_ID`) REFERENCES `Brand_tbl`(`ID`)
    What is the PK of Tbl_Detail? (if it's not a single Field, then post the combination of Fields which make up the combined PK)
    Code:
    FOREIGN KEY(`brand_ID`) REFERENCES `Brand_tbl`(`ID`),
    FOREIGN KEY(`Item_ID`) REFERENCES `Item_Tbl`(`ID`)
    If PID points to "records of a different Table", what is that Tables Name?
    Yes PID points to "records of the Main_Tbl"

    As for the last request #5) ... you can easily do that in "Break-Mode" in the Immediate-Window via:
    ?Cnn.Databases("main").Tables("TheTableName").SQLForCreate
    I'm getting error 91: Object variable not set

    But By curiosity could you please test this scenario and tell me what RecordCount you have?
    Code:
    Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
      Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
      
      Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
        Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
    Do you have 12 as I have?
    Thank you

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    689

    Re: Need help with this query

    put SELECT DISTINCT on the query to fix this
    I can't use DISTINCT as "Item_ID" need sometimes to be duplicated

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    689

    Re: Need help with this query

    Quote Originally Posted by Shaggy Hiker View Post
    This is clearly a database question, not a programming question. Normally, I would just move it, but in the original post, you asked that the thread not be moved. Why is that?
    In fact I have never posted elsewhere
    And I think people here are very helpful

  17. #17
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,950

    Re: Need help with this query

    Quote Originally Posted by newbie2 View Post
    In fact I have never posted elsewhere
    And I think people here are very helpful
    GREAT! Now you can learn TWO new things...or even more.

    Now you have the thread where it belongs, you'll find some of the same people, along with a bunch of other folks who are very good at queries, and you'll have spread your wings a bit wider. As a bonus, there's still a link to the thread in the old location.
    My usual boring signature: Nothing

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    689

    Re: Need help with this query

    As a bonus, there's still a link to the thread in the old location.
    Thank you sir for your kindness

  19. #19
    PowerPoster
    Join Date
    Jun 2013
    Posts
    5,368

    Re: Need help with this query

    Quote Originally Posted by newbie2 View Post
    Code:
    FOREIGN KEY(`PID`) REFERENCES `Main_Tbl`(`ID`),
    FOREIGN KEY(`Item_ID`) REFERENCES `Item_Tbl`(`ID`),
    FOREIGN KEY(`brand_ID`) REFERENCES `Brand_tbl`(`ID`)

    Code:
    FOREIGN KEY(`brand_ID`) REFERENCES `Brand_tbl`(`ID`),
    FOREIGN KEY(`Item_ID`) REFERENCES `Item_Tbl`(`ID`)
    Defining Foreign Key-Fields in SQLite does not auto-generate any indexes,
    nor is a combination of Foreign-Key-Fields auto-generating a Primary Key on the table in question.

    If you have intermediate tables like that, which consist primarily of Foreign-Key-Fields,
    you should manually add a combined PK (out of the ID-Fields which make a record unique) in your Table-Creation.

    Could you describe more clearly, what the purpose of the 2 tables (Tbl_Debt and Tbl_Details) is,
    and where they differ? (what is the Field DEPT in Tbl_Debt used for?)

    For my taste, you hold too many ForeignKeys "per intermediate table".
    (I usually restrict myself to only two - the Intermediate-Table then connecting only two "real tables").

    I'm absolutely sure that, when you break these two tables up into probably only 3 -
    with nice, descriptive names like e.g.:
    - tbl_Brand_Item (connecting table Brand with table Item)
    - tbl_Main_Item (connecting only table Main with table Item)
    - tbl_Main_Brand (connecting only table Main with table Brand)

    Also, what is the meaning of the table "Main" (is it Products, or Persons)?

    Quote Originally Posted by newbie2 View Post
    I'm getting error 91: Object variable not set
    That's explainable, if you were not properly (as I wrote) in "break mode" ...
    (meaning, you have to run your App so that the Cnn-Object got established -
    then break - before asking the Cnn-Object to deliver some info in the Immediate-Window).

    Please post your Table-Schema-Defs ...
    There's a whole lot of recommendations we can only give, when you do so...

    If you don't want to loose yourself in more "wondrous behaviour of the DB" in the future,
    now is the time to overhaul all your Table-Defs in a consistent manner:

    Entity-Tables (tables which store "real data"):
    - choose proper names for them (not something like "Main" - and also avoid any underscores in an Entity-Table-Name)
    - consistently named ID-Fields in your Entity-Tables

    Connection-Tables (tables which typically consist of only two ForeignKey-Fields):
    - the table-naming here is simple: if you connect Product with Brand - the name should be: Product_Brand (only in these tables, underscores are allowed)
    - the two Fields in such a Table should follow the Entity-TableName as well: (Product_ID, Brand_ID)
    - define a unique (combined) PK on these Connection-Tables in addition

    If you follow these rules in your new Schema-Def, connections across more than 2 Entity-Tables will behave properly.


    HTH

    Olaf
    Last edited by Schmidt; Feb 9th, 2021 at 03:21 PM.

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

    Re: Need help with this query

    Quote Originally Posted by newbie2 View Post
    I can't use DISTINCT as "Item_ID" need sometimes to be duplicated
    You totally missed my point. Using SELECT DISTINCT is evil, bad - wrong - in nearly 99% of the places it is used!

    And if that is all you got out of the effort I put into taking your data and SQL and testing it and telling you why you got 4 rows, I'm speechless.

    *** 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

  21. #21

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    689

    Re: Need help with this query

    what is the meaning of the table "Main" (is it Products, or Persons)?
    the Main_Tbl holds persons.(Customers)

    Could you describe more clearly, what the purpose of the 2 tables (Tbl_Debt and Tbl_Details) is,
    and where they differ? (what is the Field DEPT in Tbl_Debt used for?)
    The Tbl_Debt holds the ID of cusomers in Main_Tbl in PID field, the Item_ID, brand_ID and the debt owed.
    Tbl_Details holds also the ID of cusomers , Item_ID, brand_ID, cash, description and other fields...
    Perhaps I badly structured my database
    However I found a workaround
    I added an autoincrement field 'autoID' to Tbl_Details then
    Code:
    StrSql = "Select * from Item_Tbl I " & _
    " inner join Tbl_Debt D on I.ID = D.Item_ID " & _
    " inner join Tbl_Detail DT on I.ID  = DT.Item_ID And  D.PID = DT.PID" & _
    " where D.PID = 1 Group by autoID"
    I get exactly what I expect.
    Thank you Olaf very much
    You are very helpful.

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