StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
4637294
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-01-09T02:11:05.857
FavoriteCount
0
LastActivityDate
2011-01-09T02:25:26.850
LastEditDate
2011-01-09T02:25:26.850
LastEditorUserId
448568
OwnerUserId
448568
ParentId
4551006
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
Its possible using a combination of <code>stored procedures</code>, <code>in-memory tables</code> and control flow using a <code>WHILE</code> loop: <pre><code>DELIMITER $$ DROP PROCEDURE IF EXISTS `Get10ProjectsPerUserByStatus`$$ -- 1. DEFINE STORED PROCEDURE CREATE PROCEDURE `Get10ProjectsPerUserByStatus`(_status INT) BEGIN -- 2. DECLARE VARIABLES AND IN-MEMORY TABLES DECLARE _id INT; DROP TABLE IF EXISTS temp_user; DROP TABLE IF EXISTS temp_project_user; CREATE TABLE temp_user (id INT) ENGINE=MEMORY; CREATE TABLE temp_project_user (p_id INT, u_id INT) ENGINE=MEMORY; -- 3. ADD ALL USERS AND LOOP BY REMOVING 1 USER AT A TIME INSERT INTO temp_user SELECT id FROM users; WHILE (SELECT COUNT(*) FROM temp_user) > 0 DO SET _id = (SELECT MIN(id) FROM temp_user); INSERT INTO temp_project_user SELECT ps.project_id, _id FROM project_skills ps JOIN expert_skills es ON ps.skill_id = es.skill_id WHERE es.expert_id = _id LIMIT 10; DELETE FROM temp_user WHERE id = _id; END WHILE; -- 4. SELECT FROM IN-MEMORY TABLE AND JOIN TO EXISTING SCHEMA SELECT DISTINCT t.u_id AS expert_id, u.firstname, u.lastname, t.p_id AS project_id, p.project_title, p.project_budget, p.created AS project_created FROM temp_project_user t INNER JOIN users u ON u.id = t.u_id INNER JOIN projects p ON p.id = t.p_id; -- 5. DROP IN-MEMORY TABLES DROP TABLE temp_user; DROP TABLE temp_project_user; END$$ DELIMITER ; -- 6. CALL STORED PROCEDURE & DROP WHEN FINISHED CALL Get10ProjectsPerUserByStatus(1); DROP PROCEDURE IF EXISTS `Get10ProjectsPerUserByStatus`; </code></pre> NOTE: Going beyond the question here but it is actually possible to apply the 10 project limit as a parameter of the stored procedure by using the same logic: <pre><code>-- GET 10 PROJECTS PER USER WITH STATUS = 1 CALL GetProjectsPerUserByStatus(1, 10) </code></pre> To do this you'd need 1 additional <code>in-memory table</code> and another <code>WHILE</code> loop
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POComplicated SQL Query About Joining And Limitting
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USSteven de Salas
UserOwnerUserId
1. USSteven de Salas
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.