Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
asd
Black Wall
Comp
2
Islands demonstration
hw
Atividade 2
sss
SQL - Unpivot Table
Descriptor test