CODEWITHSUNDEEP
phpmysqlsql
Steven
Steven

Reputation: 13985

Select all threads with tag & the rest of the tags?

I am trying to get all the threads with tag name "test" for example, along with all the other tags. If I add a where clause to my INNER JOIN select then it gets the threads with that tag, but it doesn't get the remaining tags for that thread (each tag is a separate row, I use the group concat to combine them). How can I get the remaining tags also?

I have the following SQL

SELECT `threads`.`id`,
       `threads`.`title` AS `title`,
       `threads`.`created_at` AS `created_at`,
       `threads`.`views` AS `views`,
       `threads`.`comments` AS `comments`,
       `threads`.`user_id` AS `user_id`,
       `tags`
FROM `threads`
INNER JOIN
  (SELECT threads_id,
          GROUP_CONCAT(DISTINCT `thread_tags`.`thread_tags_title`) AS tags
   FROM `thread_tags`
   WHERE `thread_tags`.`thread_tags_title` = 'test'
   GROUP BY threads_id) thread_tags ON `threads`.`id` = `thread_tags`.`threads_id`
WHERE `threads`.`status` = '1'
ORDER BY `threads`.`views` DESC, `threads`.`created_at` DESC LIMIT 25
OFFSET 0

And the following schemes

Tags

CREATE TABLE `tags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `status` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `doctors` int(11) DEFAULT NULL,
  `threads` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

Thread Tags (table with thread id and tag id)

CREATE TABLE `thread_tags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `threads_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  `thread_tags_title` varchar(255) NOT NULL DEFAULT '',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

Threads

CREATE TABLE `threads` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `body` text NOT NULL,
  `status` int(11) NOT NULL,
  `views` int(11) NOT NULL,
  `rating` int(11) NOT NULL,
  `comments` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `metadata` text,
  PRIMARY KEY (`id`),
  KEY `title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

Upvotes: 1

Views: 78

Answers (1)

Petah
Petah

Reputation: 46060

Something like

SELECT threads.*, GROUP_CONCAT(tags.title)
FROM threads AS t
LEFT JOIN thread_tags AS tt ON t.id = tt.threads_id
LEFT JOIN tags AS tt ON tt.tag_id = tags.id
WHERE t.id IN (
    SELECT tt.threads_id
    FROM thread_tags AS tt
    JOIN tags ON tt.tag_id = tags.id 
             AND tags.title = "test"
)
GROUP BY t.id

Upvotes: 1

Related Questions