MySQL Query: GROUP BY without loosing rows in result -

i have written mysql query, not quite happy output.

the query looks this:

select    f.id,   s.time,   f.name,   f.value    entries s,   entry_details f    f.id = s.id , (f.name = 'fruit' or f.name = 'vegetable'); 

and produces result:

id | time       | name      | value ---|------------|-----------|-------- 4  | 2016-01-14 | fruit     | banana 4  | 2016-01-14 | vegetable | carrot 5  | 2016-01-14 | fruit     | apple 5  | 2016-01-14 | vegetable | corn 

while info required in there, wondering if there way results f.id 1 row?

using group f.id achieved wanted, vegetable rows disappears.

is possible mysql alone?

yes, need combine(aggregate) rest columns:

select    f.id,   max(s.time) `time`,   group_concat(f.name) `name`,   group_concat(f.value) `value`  entries s join entry_details f    on f.id = s.id  f.name in('fruit', 'vegetable') group f.id; 

avoid using old comma join syntax, use newer join instead.

output:

╔════╦════════════╦═════════════════╦════════════════╗ ║ id ║    time    ║       name      ║     value      ║ ╠════╬════════════╬═════════════════╬════════════════╣ ║  4 ║ 2016-01-14 ║ fruit,vegetable ║ banana, carrot ║ ║  5 ║ 2016-01-14 ║ fruit,vegetable ║ apple, corn    ║ ╚════╩════════════╩═════════════════╩════════════════╝