CODEWITHSUNDEEP
mysql
John Smith
John Smith

Reputation: 495

selecting tags for respective item

I am trying to get each item, and their respective tags with one query given a user_id. 

  CREATE TABLE IF NOT EXISTS `items` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(255) NOT NULL,
      `description` mediumtext NOT NULL,
      `user_id` bigint(100) NOT NULL,
      `to_read` tinyint(1) NOT NULL DEFAULT '0',
      `added_at` datetime NOT NULL,
      `created_at` datetime NOT NULL,
      `updated_at` datetime NOT NULL,
      PRIMARY KEY (`id`),
      KEY `user_id` (`user_id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(100) NOT NULL,
  `item_id` int(11) NOT NULL,
  `tag` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `user_id` (`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query:

SELECT tags.tag FROM tags WHERE tags.item_id = 5;

SELECT tag FROM tags t JOIN  items item ON t.item_id = item.id AND t.item_id = 5;

Which of my queries is correct to retrieve each items TAGS!

Upvotes: 0

Views: 74

Answers (1)

maja
maja

Reputation: 18034

If you only want to get the tags, use

SELECT tag FROM tags WHERE item_id = 42;

To get the item AND the tags you need "Left outer join". Otherwise, items with no tags will be ignored:

SELECT tag FROM tags t LEFT JOIN  items item ON t.item_id = item.id AND t.item_id = 42;

Note, however, that might get the same tag multiple times. To avoid this, use SELECT DISTINCT ... instead.

Edit: The following statement returns one row per item, with an additional column that concatenates all tags of that item:

SELECT items.*, 
    (SELECT GROUP_CONCAT(tags.tag) 
       FROM tags 
      WHERE tags.item_id = items.id) AS tags 
FROM items WHERE items.user_id = 0;

In case that you decide to switch to a three-table layout with a tags_items table:

SELECT items.*, 
    (SELECT GROUP_CONCAT(tags.tag) FROM tags 
       WHERE tags.id = tags_items.tag_id) AS tags 
  FROM items 
  LEFT JOIN tags_items 
         ON tags_items.item_id = items.id 
 WHERE items.user_id = 0;

Upvotes: 1

Related Questions