CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
username TEXT
);
INSERT INTO users (username) VALUES ('jim');
INSERT INTO users (username) VALUES ('bob');
INSERT INTO users (username) VALUES ('joe');
CREATE TABLE ideas (
idea_id INTEGER PRIMARY KEY,
idea TEXT,
created_by INTEGER
);
INSERT INTO ideas (idea, created_by) VALUES ('free coffee', 1);
INSERT INTO ideas (idea, created_by) VALUES ('long breaks', 1);
INSERT INTO ideas (idea, created_by) VALUES ('quit early', 3);
CREATE TABLE users_ideas (
user_id INTEGER,
idea_id INTEGER,
rating INTEGER,
PRIMARY KEY (user_id, idea_id)
);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (1, 1, 100);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (1, 2, 100);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (3, 3, 100);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (1, 3, 50);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (3, 1, 25);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (2, 1, 75);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (2, 2, 85);
SELECT i.idea_id, i.idea, u.username AS created_by_name FROM ideas i
JOIN users u ON i.created_by = u.user_id;
idea_id idea created_by_name
---------- ----------- ---------------
1 free coffee jim
2 long breaks jim
3 quit early joe
SELECT i.idea_id, i.idea, u.username AS created_by_name FROM ideas i
JOIN users u ON i.created_by = u.user_id WHERE u.user_id = 1;
idea_id idea created_by_name
---------- ----------- ---------------
1 free coffee jim
2 long breaks jim
SELECT i.idea_id, i.idea, u.username AS created_by_name, rating FROM
ideas i JOIN users u ON i.created_by = u.user_id JOIN users_ideas ui
ON i.created_by = ui.user_id WHERE u.user_id = 1;
idea_id idea created_by_name rating
---------- ----------- --------------- ----------
1 free coffee jim 100
1 free coffee jim 100
1 free coffee jim 50
2 long breaks jim 100
2 long breaks jim 100
2 long breaks jim 50
SELECT i.idea_id, i.idea, u.username AS created_by_name, rating FROM
ideas i JOIN users u ON i.created_by = u.user_id LEFT JOIN users_ideas
ui ON i.created_by = ui.user_id WHERE u.user_id = 1;
idea_id idea created_by_name rating
---------- ----------- --------------- ----------
1 free coffee jim 100
1 free coffee jim 100
1 free coffee jim 50
2 long breaks jim 100
2 long breaks jim 100
2 long breaks jim 50
Given a specific user_id (say, user_id = 1) how on earth do I get the following?
idea_id idea created_by_name rating
---------- ----------- --------------- ----------
1 free coffee jim 100
2 long breaks jim 100
3 quit early joe NULL
--
Puneet Kishor
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
