StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
2482583
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2010-03-20T09:59:30.493
FavoriteCount
0
LastActivityDate
2010-03-20T10:20:02.940
LastEditDate
2010-03-20T10:20:02.940
LastEditorUserId
37367
OwnerUserId
37367
ParentId
2461801
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
Yet another approach is to slightly denormalise your design to better support your query. To do this, create a new table "role": <pre><code>create table role ( user_id int not null, role enum ('writer', 'editor', 'publisher' ) not null, primary key (user_id, role ) ); </code></pre> This would be updated whenever a new row is added to one of your link tables that includes a user_id: <pre><code>insert ignore into role values( $user_id, $role ); </code></pre> After a while, it is likely that the role entry will already exist, hence the "ignore" modifier. The table could be boot-strapped from your existing tables: <pre><code>insert ignore into role select distinct user_id, 'writer' from link_writers; insert ignore into role select distinct user_id, 'editor' from link_editors; insert ignore into role select distinct user_id, 'publisher' from link_publishers; </code></pre> Your search query then becomes a set of simple JOINS which MySQL should have no problem optimising: <pre><code>SELECT r.user_id, r.role, case r.role when 'writer' then w.publication_id when 'editor' then e.publication_id when 'publisher' then pg.publication_id end as publication_id FROM ( (SELECT user_id FROM users WHERE firstname LIKE '%Jenkz%') UNION (SELECT user_id FROM users WHERE lastname LIKE '%Jenkz%') ) AS dt JOIN role r on r.user_id = dt.user_id LEFT JOIN link_writers w on r.user_id = w.user_id and r.role = 'writer' LEFT JOIN link_editors e on r.user_id = e.user_id and r.role = 'editor' LEFT JOIN link_publishers p on r.user_id = p.user_id and r.role = 'publisher' LEFT JOIN link_publisher_groups pg on p.publisher_group_id = pg.publisher_group_id; </code></pre> This will give a very "wide" answer.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POMySQL search for user and their roles
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USMartin
UserOwnerUserId
1. USMartin
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.