0

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.

Improve this question

3 Answers 3

Reset to default
1

Here's another method...

SELECT i.*
     , x.* 
  FROM injury i
  JOIN injury_list x 
    ON x.injury_id = i.injury_id
  JOIN 
     ( SELECT injury_id
            , MAX(list_id) max_list_id 
         FROM injury_list 
        GROUP 
           BY injury_id
     ) y 
    ON y.injury_id = x.injury_id 
   AND y.max_list_id = x.list_id;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thanks Strawberry for thinking with me! I ended up picking the one from Joachim Isasksson.
0

...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.

Improve this answer

1 Comment

Thanks! This really helped me out, the sad thing is I don't understand a thing of what you did here. The fiddle showed me all i wanted to know. So copy pasted and worked!
0

You can do this by matching to a subquery that gets the latest comment_added for each injury_id.

SELECT
  b.injury_id, bl.injury_id b.name, b.body_part,
  b.first_mention, b.last_changed, b.status
FROM injury b
INNER JOIN injury_list bl ON b.injury_id = bl.injury_id
WHERE (bl.injury_id, bl.comment_added) IN (
  SELECT injury_id, MAX(comment_added)
  FROM injury_list
  GROUP BY injury_id)
ORDER BY status, last_changed DESC
Improve this answer

6 Comments

This is OK, but note that in most cases an uncorrelated subquery will give better performance.
But the subquery in my answer is non-correlated, isn't it? It doesn't reference the outer query at all.
Yes, but it's not an "uncorrelated subquery" :-) - it's an IN clause, which is just about the worst thing you can do (although, to be fair, all these things are now blisteringly fast!)
Hmm, I'll have to look up "uncorrelated" vs "non-correlated" - always something else to learn, which is good, kinda, I guess :)
@Strawberry, is the answer you provided above an example of an "uncorrelated subquery"? If so, I get it. I use that approach (with a LEFT JOIN and a NULL check) to avoid NOT IN but I didn't think you needed to be so careful to avoid IN. Still, I like it!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.