mysql complicated join

Question

I have run into some troubles while writing a query for MySQL. I don't know how to describe my problem well enough to search the web for it, so sorry if my question is stupid.

I have 3 tables:

CREATE TABLE posts( id INT, author INT );
CREATE TABLE users( id INT, nick varchar(64) );
CREATE TABLE groups( id INT, name varchar(64) );
CREATE TABLE membership (user INT, group INT, date INT ) ;

Membership contains info about users that have joined some groups. "Date" in the membership table is the time when a user joined that group.

I need a query which will return a post, its author's nick and the name of the group with the least joining date.

All I have currently is:

SELECT p.id, u.nick, g.name
FROM posts AS p 
    LEFT JOIN users AS u ON u.id = p.author 
    LEFT JOIN membership AS m ON m.user = p.author
    LEFT JOIN groups AS g ON g.id = m.group
WHERE 1;

but of course it returns a random group's name, not the one with earliest joining date.

I also tried the following variant:

SELECT p.id, u.nick, g.name
FROM posts AS p 
    LEFT JOIN users AS u ON u.id = p.author 
    LEFT JOIN 
              (SELECT * FROM membership WHERE 1 ORDER BY date ASC) 
              AS m ON m.user = p.author
    LEFT JOIN groups AS g ON g.id = m.group
WHERE 1;

but it gave me same result.

I would appreciate even pointers to where I could start, because at the moment I have no idea what to do with it.

Proper [sample code](http://sscce.org/) (which, for SQL questions, means `CREATE TABLE` statements) is more helpful than ad hoc schema. Note also that making auto-incremented columns unsigned doubles the number of generated values (not that the tables in the question are likely to reach that many entries). — outis, Dec 17 '11 at 21:39
Thanks, i have learned english from movies, games and technical documentation, so i think it's good that i can at least write something that english-speaking people can understand. But next month i plan to take some lessons to learn it better. — navij, Dec 17 '11 at 22:18

Andreas Wederbrand · Answer 1 · 2011-12-17T21:56:49.267

0

Something like this

SELECT p.id, u.nick, g.name
  FROM posts p,
       users u,
       membership m,
       groups g
 WHERE p.author = u.id
   AND m.user = u.id
   AND m.group = g.id
 ORDER BY m.date ASC
 LIMIT 1;

Take care to have good indexes when joining these 4 tables.

edited Dec 17 '11 at 21:56

answered Dec 17 '11 at 21:23

Andreas Wederbrand

38,065
11
68
78

Don't forget the comma after "membership m". – Jim Miller Dec 17 '11 at 21:35
No, sorry, such query still returns some random stuff in g.name. – navij Dec 17 '11 at 21:41
[Implicit joins](http://stackoverflow.com/questions/44917/) shouldn't be used in favor of [explicit joins](http://stackoverflow.com/questions/2241991/). – outis Dec 17 '11 at 22:05
@outis I feel that is up to taste. It even says in the post you link that there is no performance difference between the two and that is all that matters to me. I'm sure some people like one over the other and that is fine. People have to make there own choice. – Andreas Wederbrand Dec 18 '11 at 08:08
@Andreas: development time is as important as run time. Where explicit joins provide advantage is in development; they are clearer as to purpose and more maintainable. Can you name any advantage to implicit joins that doesn't boil down to "habit"? – outis Dec 18 '11 at 09:50
... Also note that the linked answer states there is no performance different for simple joins, but for complex joins, there are additional optimizations the DBMS can perform. – outis Dec 18 '11 at 10:03
I don't disagree but in this case it wasn't a complex join. And for me it's more readable with commas than with joins. I write joins when I have to, like outer joins and straight joins. – Andreas Wederbrand Dec 18 '11 at 10:14
@Andreas: consistency is important, too. Using one syntax for some joins and another for others isn't particularly consistent of expression. I'd agree with you that using implicit joins isn't in any sense invalid, since the differences have more to do with the developers than programs, but explicit has all the advantages, in as close to an objective sense as you can get. – outis Dec 31 '11 at 21:03

score 0 · Answer 2 · answered Dec 17 '11 at 21:37

I don't know why you want what you do, however, if you want the information for the earliest membership date (since there's no date for posting itself), no problem. Now, we have the earliest membership which will always point to the same one person as you are not asking for a specific group.. (or did you want the earliest person PER membership group -- which is what I'll write the query for). Now, we have the earliest user and can link to the posts table (by apparently the author), but what if someone has 20 posts under their name... Do you also want the FIRST ID for that author.

Just copying from your supplied tables as a reference... posts: id (int), author(int) users: id (int), nick (varchar) groups: id (int), name (varchar) membership: user (int), group (int), date (int)

select
      u1.nick,
      m2.date,
      g1.name,
      p1.id as PostID
   from 
      ( select m.group, 
               min( m.date ) as EarliestMembershipSignup
           from
              Membership m
           group by 
              m.group ) EarliestPerGroup

      join Membership m2
         on EarliestPerGroup.Group = m2.Group
        AND EarliestPerGroup.EarliestMembershipSignup = m2.Date

         join groups g1
            on m2.group = g1.id

         join users u1
            on m2.user = u1.ID

            join posts p1
               on u1.id = p1.author

sorry if my question was unclear. Originally, i have to show some posts, and for each i show also it's author nickname and link to a group that he have joined earlier than other groups (first membership record of this users's memberships). — navij, Dec 17 '11 at 21:49
maybe i just should keep a first group that user will join in `users`, as an additional field. It'd will free me of triple joins for each post in forum, which is pretty good for performance — navij, Dec 17 '11 at 21:54

score 0 · Answer 3 · answered Dec 17 '11 at 22:02

0

I'd recommend moving your date column from the membership table into your groups table since that seems to be where you're tracking that information. The membership table is just an intersection table for the many-to-many users<->groups tables. It should only contain user ID and the group ID columns.

What about this?

SELECT p.id, u.nick, g.name 
FROM 
  users u, 
  posts p, 
  groups g
INNER JOIN membership m
  ON u.id = m.user
INNER JOIN groups 
  ON m.group = groups.id
ORDER BY g.timestamp DESC
LIMIT 1;

answered Dec 17 '11 at 22:02

Jim Miller

426
5
14

Yes, it is an intersection for the many-to-many users<->groups. But in `members` also is stored a timestamp of a moment, when a connection between this specific user and that specific group has been established. And i need to get a first (by timestamp) connection of that user. – navij Dec 17 '11 at 22:28

mysql complicated join

3 Answers3