How to make reference to another tables? (SQLite)

Question

I am making a list of Images with Tags so I decided to make a SQLite Database with ImagesPath and Tags. I know that I have to combine these two tables in the state many do many but i really don't know how.

I have three tables:

1)ImagesPath (INT id, TEXT imagePath)

2)Tags(INT id, TEXT title)

3)ImageTag(INT id, image_id, tag_id)

in this fields I have to reference to ImagesPath.id and Tags.id but I dont know how. Can anybody help me?

But I can make only query (example: SELECT) with this JOIN or i have to JOIN this tables when i create third table? Do i really need this third table (ImageTag)? — MrFisherman, Jan 02 '19 at 19:43

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

You use JOIN's for example you could use :-

SELECT imagePath, title FROM ImagesPath 
    JOIN ImageTag ON image_id = ImagesPath.id
    JOIN Tags ON tag_id = Tags.id

join-clause

Working Example

Consider the following which drops and creates the 3 tables and then populates the tables and then finally runs a query :-

DROP TABLE IF EXISTS ImagesPath;
DROP TABLE IF EXISTS Tags;
DROP TABLE IF EXISTS ImageTag;
CREATE TABLE IF NOT EXISTS ImagesPath (id INTEGER, ImagePath TEXT);
CREATE TABLE IF NOT EXISTS Tags (id INTEGER, title TEXT);
CREATE TABLE IF NOT EXISTS ImageTag (id INTEGER, Image_id INTEGER, Tag_id INTEGER);
INSERT INTO ImagesPath (id,Imagepath) VALUES (1,'Image1'),(2,'Image2'),(3,'Image3');
INSERT INTO Tags (id,title) VALUES (1,'TagA'),(2,'TagB'),(3,'TagC');
INSERT INTO ImageTag (Image_id,Tag_id) VALUES (1,3),(2,1),(2,2),(2,3),(3,1),(3,3);

SELECT imagePath, title FROM ImagesPath 
    JOIN ImageTag ON image_id = ImagesPath.id
    JOIN Tags ON tag_id = Tags.id;

The result will be :-

Note that no values have been assigned to the id column of the ImageTag table, this value would probably be of little use as the ImageTag table is basically mapping image to tag(s) (allowing many to many relationships).
Note that you have to be a little careful with amibiguities e.g. there are 3 id columns hence id being prefixed with the table name and a period seperator as per ImagesPath.id and also Tags.id

How to make reference to another tables? (SQLite)

1 Answers1

Working Example