CODEWITHSUNDEEP
mysqlsqltags
krenkz
krenkz

Reputation: 470

How to create an sql query for a tag system with multiple tags?

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.

Upvotes: 2

Views: 763

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 3

Related Questions