One minor quibble on the syntax of your sample query.
After spending hours searching for the missing or extra comma I now format all my queries like this
SELECT
id_employeeid -- this is the primary kep for the employee table
, employee_name -- specify what portion of the table you are looking at
, employee_ID
, department -- I would tend to clarify this as well as department might also be a table not a field
, start_date
FROM
employees_table -- redundant I know but then it's obvious what you are looking at
WHERE
start_date > "2021-12-31"
AND department != "sales"
I nearly always bring in the primary key in most queries because it often makes it easier to do further processing on the results.
Here’s an example of one of my table creation statements copied direct out of Obsidian
-- The sire_id and dam_id are pointers into this table
-- id_breeder_id_contactsid is a pointer into the contacts table of the person and location that is the breeder.
CREATE TABLE "animal_table"
("id_animalid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE
, "animal_name" TEXT NOT NULL
, "id_sexid" INTEGER NOT NULL
, "birth_date" TEXT NOT NULL
, "birth_time" TEXT NOT NULL
, "id_birthtypeid" INTEGER NOT NULL
, "birth_weight" REAL NOT NULL
, "birth_weight_id_unitsid" INTEGER NOT NULL
, "id_birthinghistoryid" INTEGER NOT NULL
, "rear_type" INTEGER NOT NULL
, "weaned_date" TEXT NOT NULL
, "death_date" TEXT NOT NULL
, "id_deathreasonid" INTEGER NOT NULL
, "sire_id" INTEGER NOT NULL
, "dam_id" INTEGER NOT NULL
, "inbreeding" REAL NOT NULL
, "id_breeder_id_contactsid" INTEGER NOT NULL
, "id_managementgroupid" INTEGER NOT NULL
, "alert" TEXT NOT NULL
)
Here is a medium complex query. I use table and field names so even the novice understands what I am doing. Yes, it makes for more typing but that also helps future me who may not totally remember why and how I did something.
SELECT
animal_table.id_animalid
,(SELECT
contacts_table.contact_first_name
FROM contacts_table
WHERE
animal_ownership_history_table.to_id_contactsid = contacts_table.id_contactsid) AS owner_first_name
, (SELECT
contacts_table.contact_last_name
FROM contacts_table
WHERE
animal_ownership_history_table.to_id_contactsid = contacts_table.id_contactsid) AS owner_last_name
, (SELECT
contacts_table.contact_company
FROM contacts_table
WHERE
animal_ownership_history_table.to_id_contactsid = contacts_table.id_contactsid) AS Company
, (SELECT
tag_number
FROM animal_id_info_table
WHERE
id_tagtypeid = "2"
AND animal_id_info_table.id_animalid = animal_table.id_animalid
AND (tag_date_off IS NULL OR tag_date_off = '')
)
AS RFID
, (SELECT
tag_number
FROM animal_id_info_table
WHERE
id_tagtypeid = "4"
AND animal_id_info_table.id_animalid = animal_table.id_animalid
AND (tag_date_off IS NULL OR tag_date_off = '')
)
AS Ranch_Tag
,(SELECT
tag_number
FROM animal_id_info_table
WHERE
id_tagtypeid = "10"
AND animal_id_info_table.id_animalid = animal_table.id_animalid
AND (tag_date_off IS NULL OR tag_date_off = '')
)
AS Trich_Tag
, animal_evaluation_table.trait_score11 as Scrotal_Circ_cm
, animal_evaluation_table.trait_score12 as Motility_percent
, animal_evaluation_table.trait_score13 as Morphology_percent
, (SELECT
note_text
FROM animal_note_table
WHERE
animal_note_table.id_animalid = animal_table.id_animalid
AND note_date > "2022-05-10%"
)
AS Comments
, custom_evaluation_traits_table.custom_evaluation_item as Classification
, animal_table.birth_date as Birth_Date
FROM animal_table
INNER JOIN animal_evaluation_table ON animal_evaluation_table.id_animalid = animal_table.id_animalid
INNER JOIN custom_evaluation_traits_table ON custom_evaluation_traits_table.id_custom_evaluationtraitsid = animal_evaluation_table.trait_score16
LEFT JOIN animal_ownership_history_table on animal_ownership_history_table.id_animalid = animal_table.id_animalid
WHERE
--id_contactsid = 1
--AND
animal_evaluation_table.eval_date > "2022-05-10"
ORDER BY
owner_last_name
, Trich_Tag
```