I have 3 tables and do not know how to create a series of sql queries (or if possible just one complex query). The tables are:
CREATE TABLE IF NOT EXISTS excerpt(
excerptID INT UNSIGNED NOT NULL AUTO_INCREMENT,
author VARCHAR(45) NOT NULL,
title VARCHAR(255) NOT NULL,
text VARCHAR(1000) NOT NULL,
comments VARCHAR(1000) NOT NULL,
PRIMARY KEY (excerptID)
) ENGINE=INNODB CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS tag(
tagID INT UNSIGNED NOT NULL AUTO_INCREMENT,
description VARCHAR(45) NOT NULL ,
PRIMARY KEY (tagID)
) ENGINE=INNODB CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS tagmap (
excerptID INT UNSIGNED NOT NULL,
tagID INT UNSIGNED NOT NULL,
PRIMARY KEY (excerptID, tagID),
CONSTRAINT excerptFK FOREIGN KEY (excerptID) REFERENCES excerpt (excerptID)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT tagFK FOREIGN KEY (tagID) REFERENCES tag (tagID)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=INNODB;
Based on the description parameter, I need to return all tag descriptions related to all the excerpts bound to that description. So for example if I have four excerpts
Excerpt(1, aaaa , aaaa, aaaa, aaaa) bound via tagmap to tags (A, B, C)
Excerpt(2, bbbb, bbbb, bbbb, bbbb) bound via tagmap tags (A, D)
Excerpt(3, cccc, cccc, cccc, cccc) bound via tagmap to tag (B)
Excerpt(4, dddd, ddddd, dddd, dddd) bound via tagmap to tags (B, D)
and my parameter is "B", I want to return a list of excerptIDs and descriptions:
[Tag [excerptID=1, description=A], Tag [excerptID=1, description=B], Tag [excerptID=1, description=C], Tag [excerptID=3, description=B], Tag [excerptID=4, description=B], Tag [excerptID=4, description=D]]
So far I have this
String SQL = "SELECT e.excerptID, t.description from Excerpt AS e LEFT JOIN tagmap AS m ON m.excerptID = e.excerptID LEFT JOIN tag AS t ON t.tagID = m.tagID WHERE t.description = ?";
but that will only return this
[Tag [excerptID=1, description=B], Tag [excerptID=3, description=B], Tag [excerptID=4, description=B]]
and I can not figure out how to proceed further.
Based on the description parameter, I need to return all tag descriptions related to all the excerpts bound to that description. So for example if I have four excerpts
I believe this does what you want:
select t.*
from tags t
where exists (select 1
from tagmap tm1 join
tagmap tm2
on tm1.excerptid = tm2.excerptid join
tags t2
on tm2.tagid = t.tagid
where tm1.tagid = t.tagid and
t2.description = ?
);
This looks complicated -- perhaps because it is. But the subquery is basically getting all pairs of tags attached to the same entity. One tag (the one on tm2) is tied to the description you care about. That is the t2.description = ? in the where clause.
The other is tied to the tag in the outer table. That is the correlation clause tm1.tagid = t.tagid.
Another way to phrase it is that this query (basically the subquery):
select distinct tm1.tagid
from tagmap tm1 join
tagmap tm2
on tm1.excerptid = tm2.excerptid join
tags t2
on tm2.tagid = t.tagid
where t2.description = ?
returns all tags that are on an entity that also has the tag you are looking for.