mySQL joined tables no duplicates

Question

I have two tables: One table contains poetry submitted by members. The other is the member's table. Both tables contain The Member's ID (MID and SubMID). I want to display the last 15 poems that have been updated. However, I want to display ONLY one work for any one author.

This works but it if an author updates a few works then they get displayed many times:

SELECT * FROM submits, members
WHERE submits.SubMID = members.MID AND submits.sub_approved = 'T'
ORDER BY submits.sub_Last_Update DESC LIMIT 15

You can see the results of that query here in the rolling marquee on the right: http://www.Prose-n-Poetry.com

The problem is that one author can take over the marquee by updating a few poems.

score 1 · Accepted Answer · answered Sep 29 '13 at 03:11

1

SELECT *
FROM members m
JOIN (SELECT s.*
      FROM submits s
      JOIN (SELECT SubMID, MAX(sub_Last_Update) lastUD
            FROM submits
            WHERE approved = 'T'
            GROUP BY SubMID) l
      ON s.SubMID = l.SubMID AND s.sub_Last_Update = l.lastUD) s
ON m.MID = s.SubMID
ORDER BY s.sub_Last_Update DESC
LIMIT 15

answered Sep 29 '13 at 03:11

Barmar

741,623
53
500
612

This gave me code gave me errors. What is lastUD? I don't understand the single letters, "s", "m", "l" – user2827532 Sep 30 '13 at 05:33
The single letters are aliases for table names. They are declared after each table name. Then the letter can be used instead of the full table name all over the query. `LastUD` itself is an alias of the `MAX(sub_Last_Update)` function: it's easier to type than having to repeat the function with the parameter. – Sébastien Sep 30 '13 at 07:40
Thanks, that fixed it. The error happened because there is no field "approved" I needed to correct that to "sub_approved" – user2827532 Sep 30 '13 at 15:55

Sébastien · Answer 2 · 2013-09-29T03:19:12.870

0

Edit: As @Barmar commented, this will not give a list of the latest submission per user.

Try grouping by member

$query = "
SELECT
*

FROM
submits, members

WHERE
submits.SubMID = members.MID

AND
submits.sub_approved = 'T'

GROUP BY
submits.SubMID

ORDER BY
submits.sub_Last_Update DESC

LIMIT 15
";

edited Sep 29 '13 at 03:19

answered Sep 29 '13 at 03:10

Sébastien

11,860
11
58
78

1

Since GROUP BY is done before ORDER BY, this will get one submission per person, but not necessarily the latest one. – Barmar Sep 29 '13 at 03:12
This didn't give the last 15 updates. I have changed the code to display the last 15 new poems rather than the last 15 that have been changed (updated) by using submits.date rather than submits.sub_Last_Update. I've tried using DISTINCT to limit it to one author with no luck – user2827532 Sep 30 '13 at 05:26

mySQL joined tables no duplicates

2 Answers2