SQLite database.
I want to loop through the records in a recordset and remove particular ones based on a field content.
Trouble is it permanently deletes the record from the database even though I don’t call rs.edit or rs.update (I thought it wouldn’t).
Would it be better to copy the records I want to keep into another recordset and not delete anything?
is there a reason you can’t modify the SQL the creates the recordset to NOT include them to begin with?
Because I can’t figure out how to combine a querie on two tables in a database where one querie depends on information from another.
Table one - “Details” contains Id, s ex, dob,fatherId, motherId, of animals
Table two - “Weights” contains nameId, weight
(nameId matches Id in the first table)
I want a recordset of weights of sibling animals ie. born on the same day with the same mother (same dob and same motherId).
PS. How come you can’t say “s ex” (without the space) in this forum, it replaces it with characters.
I’ve been censored. That’s a bit prude 
[quote=236354:@Craig Grech]Because I can’t figure out how to combine a querie on two tables in a database where one querie depends on information from another.
Table one - “Details” contains Id, s ex, dob,fatherId, motherId, of animals
Table two - “Weights” contains nameId, weight
(nameId matches Id in the first table)
I want a recordset of weights of sibling animals ie. born on the same day with the same mother (same dob and same motherId).
PS. How come you can’t say “s e x” in this forum, it replaces it with characters. That’s a bit prude :)[/quote]
That sounds easy enough… are you look to extract for a specific motherid / dob?
or did you want all mothers with multiple births grouped by dob?
Not enough information provided… but it sounds like a simple join, with an appropriate where clause
PS… “Gender” is a more appropriate term anyways 
I have a subject animal and I want to graph it’s weights along with it’s siblings so the user can compare it’s progress over time.
So I need to
- Identify just the siblings that were born at the same time (maybe give or take a day in case the birth spanned midnight).
- Group and graph the weights for each sibling.
So given an ID number then
SELECT b.id,a.motherid,c.weight
from table_one a, table_one b, table_two c
where a.id=<the subject id #>
and a.mother_id=b.mother_id
and b.id=c.nameid
and abs(a.dob-b.dob)<=1this may not work EXACTLY as I wrote it… mostly becuase I don’t know how you store your dates, so they will have to be converted to a comparable value for that last piece to work… but this should give you an idea.
What I see is missing… if you are tracking the animal weight over time… shouldn’t there be a date in table_two as when the weight was taken???
and YES… table_one is referenced TWICE
Thanks I’ll transpose it to my fields and see if it works.
There’s a lot more fields in the tables include weight date and weight time (some animals are weighed several times a day).
cattle or sheep ?
hence why you track maternity and not paternity since AI means there could be many different males but only a limited number of females
unless you implant eggs as well ?
now that I think about it I suppose goats would be in the group too - we just have very few that breed them around here
Mostly Dorpers around here
Hmmm Dave,I’m just not getting it.
Is there something I can read to understand the use of a. b. etc in SQL?
Am I just meant to substiture the table names for table_1 and table_2?
Is b meant to be before a at the start or doesn’t it make any difference?
Those are called aliases in SQL. More info here. These save keystrokes, and allow clearer understanding of the query’s intent.
In your case it would be more like
Details a, Details b, Weights c
I think the wording in your post earlier made it read like the tables were Table one and Table two.
Craig, are you just trying this in Xojo code? Or do you have an SQL Editor you can play around with the queries in?
I read the info and understand how it works.
They use “AS” which made it clearer in the declaration part eg. table_1 AS a although I’ve found it still works if you omit AS.
How to construct it in this situation still eludes me.
tables:
Weights - nameid, weighdate, weight …
Details - id, dob, motherid, fatherid …
Goal: to make a recordset of all the subject animal’s sibling’s weights and weigh dates that I can graph to compare weight gain over time.
Select weight records from the weights table where nameid equals a matching id in the Details table that also has the same dob and motherid as the subject animal.
Try to construct your SQL by pieces. Get the siblings, get the siblings weights, then get the dates. Some people can write SQL manually, I need to do this visually. The premium versions of Navicat have a visual SQL builder, as have Microsoft Access and Valentina Studio (sort of).
get the free app “sqlpro for sqlite readonly” on the appstore
you will be able to play with sql requests before you put it in the xojo code it’s easier
and if you want to go further buy the sqlpro for sqlite for $20 you will be able to edit your database fully.
it’s not as advanced as navicat but so much cheaper.
You may already have one, but you don’t mention it. Just my opinion but I use the database editor so much in my work, that paying extra for a good one up front is well worth the money. I cannot say enough about how easy Navicat is to use. Easily my favorite database editor and I’ve tried a bunch of them. There are other good ones out there as well, and there are many threads on this forum where everyone lists there favorite editor. But regardless, a good editor well help you get to the bottom of complex queries a lot faster when you can edit the queries on the fly and see the results on the spot. Then it is just a matter of moving what you came up with to code.
Spending money on a tool you use a lot is worth the money, IMO. A good db tool is worth it. We use NaviCat a lot.
I recommend not even bothering with the the Xojo Database Editor.
But no tool on earth (free or otherwise) will help you solve a problem unless you have a clear understanding of the problem.
None of these tools will solve that little issue for you.