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.

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:-PatP

Thanks Pat, I'll give that direction a try and hopefully come up with something that works

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.

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

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

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

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.

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:This leaves me with a bunch of questions. My best guess at what you really want is:Give that a whirl and let us know where it takes you.

-PatP

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

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

My first thought is that SQL Lite is confused by the use of attachedDB when everything else seems to be using attached.

-PatP

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)"