使用PHP从多个表中查询MySQL数据

Question

I'm working on a database for monitoring sportinjuries. I have 2 tables, one is called injury the other one injury_list.

Injury looks like this:

-----------------------------------------------------------------------
injury_id | name   | body_part  | first_mention | last_changed | status
-----------------------------------------------------------------------
|   2     |  Ben   |   arm      |   2013-06-08  |   2013-06-13 |   0  |
|   3     |  Rick  |   knee     |   2013-05-10  |   2013-06-12 |   0  |
|   4     |  Esther|   ankle    |   2013-05-26  |   2013-06-12 |   1  |
-----------------------------------------------------------------------

and then we have injury_list which I use to store the updates from Physiotherapists and coaches

-----------------------------------------------------------------------
  list_id | injury_id | Comments               | trend | comment_added
-----------------------------------------------------------------------
|   1     |     2     | Complains a lot wo.... |   1   |   2013-06-01 |
|   2     |     2     | Gets a little bit be.. |   3   |   2013-06-08 |
|   3     |     2     | no changes so far..... |   2   |   2013-06-13 |   
|   4     |     4     | aches a lot, send t... |   1   |   2013-06-01 |
|   5     |     4     | Got a lot worse ne.... |   1   |   2013-06-08 |
|   6     |     4     | no changes so far..... |   2   |   2013-06-13 |   
-----------------------------------------------------------------------

Trend is used to show if the injury got worse (1), better(2) or no change(3)

I have an overview off all injuries where I only use the INJURY table and a detailed page per injury, where I use information from both tables this all works fine.

now I want the TREND to show on the main page in the overview, and as you can understand I only want the latest trend (based on comment_added). I tried several several queries but I can't seem to understand how to righteously call the data.

I'm not realy good with joins, and I actually don't know if that is the solution here, I hope someone can help me out:

$result = mysqli_query($con,"
SELECT b.injury_id 
     , bl.injury_id b.name
     , b.body_part
     , b.first_mention
     , b.last_changed
     , b.status 
  FROM injury b
  JOIN injury_list bl 
    ON bl.injury_id = b.injury_id 
 ORDER 
    BY status ASC
     , last_changed DESC;
");

thanks in advance for thinking with me.

Answer 1

...and here's a third way, a simple LEFT JOIN to find the latest row to eliminate the subquery;

SELECT i.*,il1.*
FROM injury i
JOIN injury_list il1
  ON i.injury_id = il1.injury_id
LEFT JOIN injury_list il2
  ON i.injury_id = il2.injury_id
 AND il1.comment_added < il2.comment_added
WHERE il2.injury_id IS NULL

An SQLfiddle to test with.

EDIT: A quick (and, I realize not entirely easy to follow) explanation; The first join is a totally normal join to get an injury and a corresponding entry in injury_list. I then LEFT JOIN to injury list again to see if there exists a newer entry. If not, the left join will leave all fields in il2 NULL (ie, non existant) and we should show the row we just built. If there exists a newer entry, the fields in il2 will have the data from the newer entry, and in that case the row should not be shown.