Sqlite query to retrieve data from many to many relationship-CodePudding

I have a sqlite database with many to many relationship, it has three tables images, tags, and a third one for many to many relationship images_tags.

Thier attributes are as: images(id,location,size,upload_date), tags(id,name), images_tags(id,image_id,tag_id)

Now i want to retrieve all image data with all its associated tags (tag id as in this case) i know joins will play the game here but i can't get around finding the right query for it. Any help would be appreciated.. i am new to this database stuff!

CodePudding user response：

The following will return a list of rows where each has the image data along with the tag.

SELECT images.*,tags.* FROM images JOIN images_tags ON images.id = images_tags.image_id JOIN tags ON tags.id = images_tags.tag_id ORDER BY images.id

e.g. something like :-

You could group according to the image (for example) and use something like :-

SELECT images.*,
    group_concat(tags.id),
    group_concat(tags.name,':') 
FROM images 
JOIN images_tags ON images.id = images_tags.image_id 
JOIN tags ON tags.id = images_tags.tag_id 
GROUP BY images.id 
ORDER BY images.id;

Which from the same data would produce :-

note that the above could be shortened as you only need table_name.column.name if the column name would be ambiguous (e.g. the id column is common to both the images and tags table and would therefore be ambiguous with the table name)

The following is the entire code used to provide the results:-

DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS images;
CREATE TABLE IF NOT EXISTS images (id INTEGER PRIMARY KEY, location TEXT, size INTEGER, upload_date TEXT);
CREATE TABLE IF NOT EXISTS tags (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE IF NOT EXISTS images_tags (id INTEGER /*<<< not required */,image_id INTEGER, tag_id INTEGER, PRIMARY KEY (image_id, tag_id));
INSERT INTO images VALUES
    (1,'loc1',10,'2022-02-23'),(2,'loc2',20,'2022-02-23'),(3,'loc3',30,'2022-02-23')
;
INSERT INTO tags VALUES
    (1,'tag1'),(2,'tag2'),(3,'tag3'),(4,'tag4'),(5,'tag5')
;
INSERT INTO images_tags VALUES
    (0,1,1),(0,1,3),(0,1,5),
    (0,2,2),(0,2,4),
    (0,3,1),(0,3,2),(0,3,3),(0,3,4),(0,3,5)
;


SELECT images.*,tags.* FROM images JOIN images_tags ON images.id = images_tags.image_id JOIN tags ON tags.id = images_tags.tag_id ORDER BY images.id;
SELECT images.*,group_concat(tags.id),group_concat(tags.name,':') FROM images JOIN images_tags ON images.id = images_tags.image_id JOIN tags ON tags.id = images_tags.tag_id GROUP BY images.id ORDER BY images.id;
DROP TABLE IF EXISTS images_tags;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS images;