Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2005
    Posts
    38

    Unanswered: Getting data from 3 tables (need help building a query)

    Hi, I have 3 tables
    CLIENT (table)
    Last_Name varchar
    First_Name varchar
    (and a bunch of other columns)

    CLIENT_COSTUME (table)
    CLIENT_ROWID integer
    COSTUME_ROWID integer

    COSTUME (table)
    ITEM varchar

    Each client will have 1 and only 1 cosutme, and the CLIENT_costume table matches the client.rowid with the costume.rowid so I was hoping I could turn my 2 sql statements into 1 ... I can't get it though.
    My 2 statements are

    rs1 = db.SQLSelect("Select CLIENT.ROWID, CLIENT.* from CLIENT")


    rs2 = BGdb.SQLSelect("Select COSTUME.ITEM, COSTUME.ROWID from CLIENT_COSTUME, COSTUME where CLIENT_COSTUME.CLIENT_ROWID = " + rs1.field("ROWID").integervalue + " and COSTUME.ROWID = CLIENT_COSTUME.COSTUME_ROWID")

    Thanks, any suggestions would be greatly appreciated, or if I need to clarify the question, let me know.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are correct, you need to turn the two different ADO statements/recordsets into a single statement/recordset. The SQL sytax to get you there is something like:
    Code:
    SELECT
       client.first_name, costume.item
       FROM CLIENT_COSTUME
       JOIN CLIENT
          ON CLIENT.rowid = CLIENT_COSTUME.CLIENT_ROWID
       JOIN COSTUME
          ON COSTUME.rowid = CLIENT_COSTUME.COSTUME_ROWID
       WHERE  criteria goes here
    -PatP

  3. #3
    Join Date
    Jun 2005
    Posts
    38
    Thanks Pat, I'll give that direction a try and hopefully come up with something that works

  4. #4
    Join Date
    Jun 2005
    Posts
    38
    2 hours later and the lightbulb just went off, there is no "where criteria goes here" because the criteria was met with the ON statement and all works well.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oops, my bad! Sometimes you want to filter the results down a smidgeon so you only get certain clients (or maybe just one client) or something like that. You are correct, in that the join criteria go in the ON clause to allow the database engine to make better choices about how it constructs the candidate rows, so the join criteria should not be included in the WHERE clause... SQL-92 syntax is a bit more expressive than SQL-89 was, but if the user and the engine both do their jobs correctly, then the SQL-92 can be a lot more efficient!

    -PatP

  6. #6
    Join Date
    Jun 2005
    Posts
    38
    I don't know if this should be in a new thread or not, but because it's similar to the above I thought I'd just continue here.

    My last task on this project is to Import records from one file to another. (same schemata) I can Import all the tables with the exception of one, the one that holds the Relations between 5 different tables. Everything I have attempted so far gives me an error such as there is no such column or I'm being ambiguous.

    The table is attached to the Database and is called tmpDB.Client_Relational and the 5 columns are Client_RowID, Body_RowID, Hair_RowID, Eyes_RowID, Shirt_RowID And I will always know the Value of Client_RowID

    The Tables Body, Hair, Eyes, Shirt all have the columns ROWID and ITEM so that Body_ROWID from tmpDB.Client_Relational would be pointing at tmpDB.body.rowid and I can easily get the ITEM

    I need the SQL to Get the tmpDB.Client_Relational.Body_RowID find the name of the ITEM then find the same ITEM in Body (the user's database) and give me the corresponding ROWID.

    I thought it would be simple, but I can't get it

    Thank you for your time

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm guessing that you are using DRI (Declared Referential Integrity) on the Client table. It probably defines the columns that reference other tables as foreign keys. This means that the database engine will verify that a given Hair_RowID exists in the Hair table before it allows a client row that uses that Hair_RowID to be inserted.

    The solution to this problem would be to populate the referenced tables (Body, Hair, Eyes, and Shirt with their respective data before you try to populate the Client table.

    -PatP

  8. #8
    Join Date
    Jun 2005
    Posts
    38
    Actually I'm using SQLite so there are no foreign keys, the data referential Integrity is done in the coding of the software itself, so there is no chance that a rowID can not be in the Referenced table.

    The Body, Hair, Eyes, and Shirt tables are populated by the User, but when the user imports from another file, yes the 4 tables are populated with any new data b4 the client table is imported, then I have just this refernce table to go. I tried something like (just one column to keep it simple)

    Select Hair.RowID from attachedDB.Client_Relational
    where attached.Client_Relational.Client_RowID = 1
    Join Hair
    ON attached.Client_Relational.Hair_RowID = attached.Hair.RowID and Hair.Item = attached.Hair.Item

    but that told me I was ambiguous.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not sure just what you want the code to do. If I can't make sense of it, I'm betting that SQL Lite can't either (although that's not a "gimmee" by any means).

    SQLLite is a C library that gives a reasonable approximation of a SQL database engine that can be embedded within your program. It parses the code you pass it, and tries to implement what you requested as best it can. It isn't terribly sophisticated, but it does a tolerable job.

    Cleaning up your code to try to bring it to the SQL-92 standard, the closest I can get is:
    Code:
    SELECT
       Hair.RowID
       FROM attachedDB.Client_Relational
       JOIN Hair
          ON attached.Client_Relational.Hair_RowID = attached.Hair.RowID
          AND Hair.Item = attached.Hair.Item
       WHERE 1 = attached.Client_Relational.Client_RowID
    This leaves me with a bunch of questions. My best guess at what you really want is:
    Code:
    SELECT
       attached.Hair.RowID
       FROM attached.Client_Relational
       JOIN attached.Hair
          ON attached.Client_Relational.Hair_RowID = attached.Hair.RowID
       WHERE 1 = attached.Client_Relational.Client_RowID
    Give that a whirl and let us know where it takes you.

    -PatP

  10. #10
    Join Date
    Jun 2005
    Posts
    38
    Hi Pat and thank you for all your help, and time. I can show you want I would like to get from the code I have "working" and maybe that will help. Though it does look like your first code is what I'm looking for.

    In BASIC I'm coding the following and after the 2nd recordSet the returned Record is giving me the hair.rowid of the user.DB that is being Updated so I can put the link into the users.client_relational table.HAIR_ROWID column with the users.client_relational.CLIENT_ROWID

    rs = BGdb.SQLSelect("
    SELECT attachedDB.hair.item
    FROM attachedDB.client_relational
    JOIN attachedDB.hair.item
    ON attachedDB.hair.rowid = attachedBB.client_relational.hair_rowid
    where attachedDB.client_relational.client_rowid = 1
    ")

    rs = BGdb.SQLSelect("
    Select rowid
    FROM hair
    WHERE hair.item = '" + rs.IdxField(1).getString +
    "'")

    This works, however like most people I'd like to do it in one step to cut down on processor ticks

    p.s. sorry I haven't profected the Posting page yet, but I think it's still ledgable

  11. #11
    Join Date
    Jun 2005
    Posts
    38
    I have tried your first code (which I think is pretty close to what I am looking for) and I get the database error

    no such column: attachedDB.hair.item

    though I know there is that column in the Attached Table hair

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first thought is that SQL Lite is confused by the use of attachedDB when everything else seems to be using attached.

    -PatP

  13. #13
    Join Date
    Jun 2005
    Posts
    38
    I replaced the rs.IdxField(1).getString with the first SQL query and it works. Thanks for your time and effort Pat to get me going the right direction.

    Select BODY.ROWID, HAIR.ROWID, EYES.ROWID, SHIRT.ROWID from BODY, HAIR, EYES, SHIRT " + _
    "where BODY.ITEM = (select tmpDB.BODY.ITEM from tmpDB.CLIENT_RELATIONAL Join tmpDB.BODY ON tmpDB.BODY.ROWID = tmpDB.CLIENT_RELATIONAL.BODY_ROWID where tmpDB.CLIENT_RELATIONAL.CLIENT_ROWID = 1) " + _
    "and HAIR.ITEM = (select tmpDB.HAIR.ITEM from tmpDB.CLIENT_RELATIONAL Join tmpDB.HAIR ON tmpDB.HAIR.ROWID = tmpDB.CLIENT_RELATIONAL.HAIR_ROWID where tmpDB.CLIENT_RELATIONAL.CLIENT_ROWID = 1) " + _
    "and EYES.ITEM = (select tmpDB.EYES.ITEM from tmpDB.CLIENT_RELATIONAL Join tmpDB.EYES ON tmpDB.EYES.ROWID = tmpDB.CLIENT_RELATIONAL.EYES_ROWID where tmpDB.CLIENT_RELATIONAL.CLIENT_ROWID = 1) " + _
    "and SHIRT.ITEM = (select tmpDB.SHIRT.ITEM from tmpDB.CLIENT_RELATIONAL Join tmpDB.SHIRT ON tmpDB.SHIRT.ROWID = tmpDB.CLIENT_RELATIONAL.SHIRT_ROWID where tmpDB.CLIENT_RELATIONAL.CLIENT_ROWID = 1)"

Posting Permissions

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