1

I have a master table with all my users. They have unique IDs. I have have 4 other tables with information the users might have coontributed to. Each have a UserID field. What I'm trying to find, is a query that will pull each user's information, and add the most recent information from each of the other tables.

UserTable
=========================
id, Name, Stuff,       deleted
 1,  Tom, More stuff,  0
 2,  Jim, Stuff,       1
 3,  Bob, Stuff again, 0
 4,  Jon, Stuff Stuff, 0
 5,  Rex, Less stuff,  1

LoginTable
=========================
id, UserID, Event
 1,      2, Logged in
 2,      2, Logged out
 3,      3, Logged in
 4,      1, Logged in
 5,      1, Logged out
 6,      1, Logged in
 7,      2, Logged in
 8,      3, Logged out
 9,      1, Logged out

CommentTable
=========================
id, UserID, Rating, Comment
 1,      2,      5, 'The food was great'
 2,      3,      2, 'Meh'
 3,      4,      3, 'I\'ve had better'
 4,      4,      1, 'Terrible'
 5,      2,      1, 'The worst'
 6,      3,      4, 'Close'
 7,      4,      5, 'The best'

MoreTables
=========================
......

Desired Output
=========================
id, Name, Stuff,       Event,      Rating, Comment,   More stuff...
 1,  Tom, More stuff,  Logged out,   NULL, NULL
 3,  Bob, Stuff again, Logged out,      4, 'Close'
 4,  Jon, Stuff Stuff, NULL,            5, 'The best'

I have been running through a loop, but I figure there is a more effecient way to do it in just a single MySQL request. Here is what I've been doing (with better error handling...):

$users_stmt = $db->query('SELECT id, Name, Stuff FROM UserTable WHERE deleted = 0');
$login_stmt = $db->prepare('SELECT Event FROM LoginTable WHERE UserID = ? ORDER BY id DESC LIMIT 1');
$comment_stmt = $db->prepare('SELECT Rating, Comment FROM CommentTable WHERE UserID = ? ORDER BY id DESC LIMIT 1');
// more queries

while ($info = $users_stmt->fetch(PDO::FETCH_ASSOC)) {
  $login_stmt->execute(array($info['id']));
  $comment_stmt->execute(array($info['id']));
  // more executes

  // I account for empty fetches...
  $info += $login_stmt->fetch(PDO::FETCH_ASSOC);
  $info += $comment_stmt->fetch(PDO::FETCH_ASSOC);
  // more fetches

  // do stuff with $info array
}

I know I can add some GROUP BY stuff to maybe improve this. I've messed around with JOINs, but nothing I do gets me there. I can LEFT JOIN two tables, but as soon as I try to include more I'm at a complete loss.

Bobb Fwed
  • 317
  • 3
  • 6
  • Left join with a subquery that gets the most recent row for each `UserID`. See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to write those subqueries. – Barmar Oct 12 '16 at 22:25
  • It seems close, but none of those solutions work with more than a single table it seems. There is no "master list" of ID's it's drawing from. Maybe I'm just not understanding how to use those methods across multiple tables from another table that has the list of IDs. – Bobb Fwed Oct 12 '16 at 22:52
  • `SELECT * FROM main_table AS t1 LEFT JOIN (subquery1) AS t2 ON t1.id = t2.UserID LEFT JOIN (subquery2) AS t3 ON t1.id = t3.UserID`. Each of those subqueries is like the queries in that question. – Barmar Oct 12 '16 at 22:55

0 Answers0