Two tables in the mix:
items
| item_id | user_id | data |
----------------------------
| 10 | 100 | A |
| 11 | 100 | C |
| 12 | 101 | E |
| 13 | 101 | G |
item_detail
| id | item_id | ignore | detail1 | detail2 | detail3 | detail4 |
-----------------------------------------------------------------
| 1 | 10 | 0 | h1 | h2 | h3 | h4 |
| 2 | 10 | 0 | g1 | g2 | g3 | g4 |
| 3 | 10 | 1 | f1 | f2 | f3 | f4 |
| 4 | 11 | 0 | e1 | e2 | e3 | e4 |
| 5 | 11 | 0 | d1 | d2 | d3 | d4 |
| 6 | 11 | 1 | c1 | c2 | c3 | c4 |
| 7 | 12 | 0 | b1 | b2 | b3 | b4 |
| 8 | 13 | 0 | a1 | a2 | a3 | a4 |
I need to find detail1,detail2 from MAX id of item_detail by item_id AND the detail3,detail4 of MAX NON IGNORED ID of item_detail by item_id, and show with item line.
Expected result:
| item_id | user_id | data | detail1 | detail2 | detail3 | detail4 |
--------------------------------------------------------------------
| 10 | 100 | A | f1 | f2 | g3 | g4 |
| 11 | 100 | C | c1 | c2 | d3 | d4 |
| 12 | 101 | E | b1 | b2 | b3 | b4 |
| 13 | 101 | G | a1 | a2 | a3 | a4 |
Here is SQLFiddle with these data sets: http://sqlfiddle.com/#!2/52839
Any takers? Your input is highly appreciated.
Thanks!
The easiest way is going to be to use group_concat() with substring_index():
select i.item_id, i.user_id, i.data,
substring_index(group_concat(id.detail order by id.id desc), ',', 1
) as last_detail,
substring_index(group_concat(case when id.ignored = 0 then id.detail1 end order by id.id desc), ',', 1
) as last_non_ignored_detail1
substring_index(group_concat(case when id.ignored = 0 then id.detail2 end order by id.id desc), ',', 1
) as last_non_ignored_detail2
substring_index(group_concat(case when id.ignored = 0 then id.detail3 end order by id.id desc), ',', 1
) as last_non_ignored_detail3
substring_index(group_concat(case when id.ignored = 0 then id.detail4 end order by id.id desc), ',', 1
) as last_non_ignored_detail4
from items i join
item_detail id
on i.item_id = id.item_id
group by i.item_id;