1

i have the following table

table 1

------------------------------------------
| animal_id | animal_name | animal_type |
------------------------------------------
|     1     | kay         | cat         |
|     2     | sandy       | dog         |
------------------------------------------

table 2

--------------------------------------------------------------------------
| vaccine_id | animal_id  | vaccine_name | vaccine_status | vaccine_date |
--------------------------------------------------------------------------
| 1          | 1          | rabies       | done           | 2015-09-09   |
| 2          | 1          | chlamydiasis | undone         | 2015-09-15   |
| 3          | 1          | parasite     | undone         | 2015-10-20   |
| 4          | 2          | parasytosis  | undone         | 2015-11-10   |
| 5          | 2          | rabies       | undone         | 2015-11-05   |

expected result:

    --------------------------------------------------------------------------------------------------------------
    | animal_id  | animal_name | animal_type | vacccine_id  | vaccine_name | vaccine_status  |  vaccine_date  |
    --------------------------------------------------------------------------------------------------------------
    | 1          | kay         | cat         | 2            | chlamydiasis | undone          | 2015-09-15     | 
    | 2          | sandy       | dog         | 5            | rabies       | undone          | 2015-11-05     |

the result will show all the first row of each unique value between two tables with the most recent vaccine_date and the condition where the vaccine_status is undone. I'm using SQL by the way

Lia Dianti
  • 139
  • 1
  • 1
  • 7
  • Proper [sample code](http://sscce.org/) (here, SQL statements) is more useful than any ad hoc schema and sample data format. Please use `CREATE TABLE` and `INSERT ... VALUES` for [samples](http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx). Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. What [work](https://mikeash.com/getting_answers.html) have you already done? – outis Dec 14 '15 at 01:25
  • well, i already tried `SELECT tbl1.animal_id, tbl1.animal_name, table1.animal_type, tbl2.vaccine_id, tbl2.vaccine_name, tbl2.vaccine_status, min(tbl2.vaccine_date) as vaccine_date FROM tbl1 LEFT JOIN tbl2 ON tbl1.anima_id = tbl2.animal_id WHERE vaccine_status = "undone" GROUP BY tbl1.animal_name` but the result only occured on vaccine_date, the rest of table 2 still only show the first row @outis – Lia Dianti Dec 14 '15 at 01:56
  • most recent Vaccine date for animal 1 looks like 2015-10-20 and for Animal 2 2015-11-10. But your expected results says something else. Do you mean most older? Or I am missing something?. – Avi Dec 14 '15 at 02:57
  • Please add updates by editing the question rather than as comments. SO is a Q&A site, rather than a forum. Also, the desired results don't match the stated conditions, as vaccine 3 is the most recent "undone" vaccine for animal 1, not vaccine 2. – outis Dec 14 '15 at 06:27
  • Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – outis Dec 14 '15 at 06:58

1 Answers1

0

For complex queries you often need to manipulate the data with something like PHP. For example, using 2 queries on the data you provided:

<?php
    $link=mysqli_connect("host","user","password","database");
    // Start with the list of animals
    $sql = "SELECT * FROM animals";
    $res = $link->query($sql);
    for($i=0; $i<mysqli_num_rows($res); ++$i){
        $row1 = mysqli_fetch_assoc($res);
        $animal_id = $row1['animal_id'];
        $sql = "SELECT * FROM vaccines WHERE animal_id = $animal_id AND vaccine_status = 'undone' ORDER BY vaccine_date ASC LIMIT 1";
        $res2 = $link->query($sql);
        $row2 = mysqli_fetch_assoc($res2);
        $arr[$animal_id] = array();
        array_push($arr[$animal_id], $row1['animal_name'], $row1['animal_type'], $row2['vaccine_id'], $row2['vaccine_name'], $row2['vaccine_status'], $row2['vaccine_date']);
    }

    echo "RESULT:";
    echo "<table border=1><tr><th>animal_id</th><th>animal_name</th><th>animal_type</th><th>vaccine_id</th><th>vaccine_name</th><th>vaccine_status</th><th>vaccine_date</th></tr>";
    foreach($arr as $key => $val){
        echo "<tr><td>$key</td><td>".implode("</td><td>", $val)."</td></tr><br>";
    }
?>

...produces the following output:

output from code

cantelope
  • 1,147
  • 7
  • 9
  • a little complicated but neat! do you know how to generate it to json? – Lia Dianti Dec 14 '15 at 02:31
  • PHP has a handy function that converts any array to into json format. json_encode($array) – cantelope Dec 14 '15 at 03:06
  • can you give me an example based on the code above? i tried it but the result isn't what i expecting – Lia Dianti Dec 14 '15 at 03:35
  • If you remove the result section and replace it with `echo json_encode($arr);` it will print the array in json format. Note that the element names will be the animal id's. – cantelope Dec 14 '15 at 04:06
  • right, i got it! now how to put the value name, like the animal_id, animal_name, etc exactly into it? – Lia Dianti Dec 14 '15 at 04:21
  • This ought to do it.. [code on codepad.org](http://codepad.org/6vlI2g4M). Hope that helps. – cantelope Dec 14 '15 at 04:36
  • nice! but it doesn't generate the array bracket in the beginning and the end, and comma for every new result – Lia Dianti Dec 14 '15 at 04:56
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/97807/discussion-between-cantelope-and-lia-dianti). – cantelope Dec 14 '15 at 05:16
  • Data should never be interpolated directly into statements. Instead, use prepared statements, which are both safer and faster. Also, PHP isn't necessary here. The desired results can be achieved entirely with SQL. – outis Dec 14 '15 at 07:00