tegs and users, PostgreSQL

tegs and users

CREATE TABLE items_tags_sets (
uit_set_id varchar PRIMARY KEY,
item_id varchar,
user_id varchar,
tag varchar
);

CREATE TABLE users (
user_id varchar PRIMARY KEY,
name varchar
);

insert into items_tags_sets values('uid1', 'it1', 'usr1', 'tag_test1');
insert into items_tags_sets values('uid2', 'it2', 'usr2', 'tag_test1');
insert into items_tags_sets values('uid3', 'it3', 'usr3', 'tag_test2');
insert into items_tags_sets values('uid4', 'it4', 'usr1', 'tag_test2');
insert into items_tags_sets values('uid5', 'it1', 'usr2', 'tag_test3');
insert into items_tags_sets values('uid6', 'it2', 'usr3', 'tag_test3');
insert into items_tags_sets values('uid7', 'it3', 'usr1', 'tag_test4');
insert into items_tags_sets values('uid8', 'it4', 'usr2', 'tag_test4');
insert into items_tags_sets values('uid9', 'it1', 'usr3', 'tag_test5');
insert into items_tags_sets values('uid10', 'it2', 'usr1', 'tag_test5');
insert into items_tags_sets values('uid11', 'it1', 'usr2', 'tag_test1');
insert into items_tags_sets values('uid12', 'it1', 'usr3', 'tag_test1');
insert into items_tags_sets values('uid13', 'it1', 'usr2', 'tag_test3');

insert into users values('usr1', 'name1');
insert into users values('usr2', 'name2');
insert into users values('usr3', 'name3');
--------------------------------------------------------------
SELECT
  items_tags_sets.item_id,
  items_tags_sets.tag,
  count(items_tags_sets.tag) as tags_count,
  items_tag_you.user_id as its_tag

FROM items_tags_sets
LEFT OUTER JOIN users as items_tag_you on items_tag_you.user_id = items_tags_sets.user_id and items_tag_you.user_id = 'usr1'
WHERE items_tags_sets.item_id = 'it1'
GROUP BY items_tags_sets.item_id, items_tags_sets.tag, items_tag_you.user_id;


run \| edit \| history \| help	0