On Monday, 12 August, 2019 14:05, dboland9 <dbola...@protonmail.com> wrote:
>Hiking_Table Trails_Table >Joining_Table >----------------- ------------------------- -- >------------------------- >hike_id PK trail_id PK >hike_id FK >hike_date TEXT trail_name TEXT trail_id FK >hike_destination TEXT trail_rating REAL >hike_rating REAL trail_comments TEXT >hike_comments TEXT >So far, so good. I know how to create the tables. What I am >struggling with is how do I insert data into the joining table or >don"t I. Somethings I have read suggest that the joining table just >contains references, so there is no actual insert. A pointer to >information how to do this would be appreciated. As for queries, I >think I use joins, but a pointer on how to do this would also be >appreciated. The problem is that you have not identified the keys properly. What you call the PK is certainly a candidate, but it is not the actual key that identifies the unique data, but rather is rather a pseudokey identifying a row, not a set of unique data identifying a row. Once you identify the actual keys, you are in business. I would guess that the actual tables probably might look as follows (I have removed the extraneous embedding since it adds nothing except more typing, and changed your "date" into a starting datetime): create table Hikes ( id INTEGER PRIMARY KEY, start TEXT NOT NULL CHECK (start IS datetime(start, '+0 days'), destination TEXT NOT NULL COLLATE NOCASE, rating REAL, comments TEXT, UNIQUE (date, destination) ); create table Trails ( id INTEGER PRIMARY KEY, name TEXT NOT NULL COLLATE NOCASE UNIQUE, rating REAL, comments TEXT ); create table HikeTrail ( hike_id INTEGER NOT NULL REFERENCES Hikes, trail_id INTEGER NOT NULL REFERENCES Trails ); create index HikedTrails on HikeTrail (hike_id, trail_id); create index TrailsHiked on HikeTrail (trail_id, hike_id); Assuming that you want to ADD a new hike on (start, destination, name), then you do the following sql: BEGIN IMMEDIATE; INSERT OR IGNORE INTO Hikes (start, destination) values (:start, :destination); INSERT OR IGNORE INTO Trails (name) values (:name); INSERT INTO HikeTrail (hike_id, trail_id) VALUES ((select id from Hikes where start=:start and destination=:destination),(select id from Trails where name=:name)); COMMIT; To DELETE a hike on (start, destination, name) you do the following: BEGIN IMMEDIATE; DELETE FROM HikeTrail WHERE hike_id = (select id from Hikes where start=:start and destination=:destination) AND trail_id = (select id from trails where name=:name); DELETE FROM Hikes WHERE start=:start AND destination=:destination AND NOT EXISTS (select hike_id from HikeTrail where hike_id = id); DELETE FROM Trails WHERE name=:name AND NOT EXISTS (select trail_id from HikeTrail where trail_id = id); COMMIT; >Perhaps these topics could be added to the excellent >http://www.sqlitetutorial.net/ . -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users