mysql - Querying aMember -

i'm trying pull query amember. have few tables:

am_user - contains user data - pk = user_id am_invoice - contains 1 line per invoice - pk invoice_id, fk user_id am_invoice_item - contains 1 line per product - fk invoice_id 

we sell 2 different products using 4 different product_ids (it's software, , products translated in 2 different versions). users can buy episode 1, episode 2, or both. (we're not interested in did not make purchase yet).

my query looks following:

 select       u.user_id, login, u.added     , case ii.item_id          when 3 i.tm_added          when 5 i.tm_added          else null end episode1bought     , case ii2.item_id         when 7 i2.tm_added         when 6 i2.tm_added         else null end episode2bought      am_user u          left join am_invoice on u.user_id = i.user_id         left join am_invoice_item ii on i.invoice_id = ii.invoice_id , ii.item_id in (3, 5)         left join am_invoice i2 on u.user_id = i2.user_id          left join am_invoice_item ii2 on i2.invoice_id = ii2.invoice_id , ii2.item_id in (7, 6) i.status = 1 , i2.status = 1 -- paid invoices order user_id 

however, ends returning 4 rows customers bought both episodes. others, bought 1 episode, works correctly:

1   user1   2013-12-07 18:06:01   episode1_en   2014-01-11 13:28:19   episode2_dk   2014-02-15 10:22:30 1   user1   2013-12-07 18:06:01   null          null                  episode2_dk   2014-02-15 10:22:30 1   user1   2013-12-07 18:06:01   episode1_en   2014-01-11 13:28:19   null          null     1   user1   2013-12-07 18:06:01   null          null                  null          null             

i'd return row containing details both purchases:

1   user1   2013-12-07 18:06:01   episode1_en   2014-01-11 13:28:19   episode2_dk   2014-02-15 10:22:30 

i bet i'm overlooking pretty simple, life of me cannot figure out went wrong. ideas how can rid of redundant rows?

i managed solve using 2 separate queries; 1 product1, , product2. in product1 query, add null column alias of episode2bought. in product2 query, same, product1bought. way can union thw 2 resultsets.

however, still lead duplicate rows in case user purchased both products. solve that, wrap select around resultset, use max() functions on productbought columns remove nulls , use group merge duplicate rows together.

the end query looks this:

select      user_id     , login     , added     , max(episode1bought) episode1bought     , max(episode2bought) episode2bought (  select  u.user_id, login, u.added     , case ii.item_id          when 3 i.tm_added          when 5 i.tm_added          else null end episode1bought     , null episode2bought       am_user u          join am_invoice on u.user_id = i.user_id         join am_invoice_item ii on i.invoice_id = ii.invoice_id , ((ii.item_id in (3, 5)) )     i.status = 1 union   select  u.user_id, login, u.added     , null episode1bought     , case ii.item_id          when 6 i.tm_added          when 7 i.tm_added          else null end episode2bought      am_user u          join am_invoice on u.user_id = i.user_id         join am_invoice_item ii on i.invoice_id = ii.invoice_id , ((ii.item_id in (6, 7)) )     i.status = 1 )  group user_id, login, added order user_id ; 

this correctly returns desired result:

1   user1   2013-12-07 18:06:01   episode1_en   2014-01-11 13:28:19   episode2_dk   2014-02-15 10:22:30