MySQL multi column match with multi row results

I am new to mySQL and tried to solve this for a day. I tried to match data from column Want1 with Offer1, and Want2 with Offer2, but the Type need to also be matched with other uid. Each uid will have only either W or O in W/O (want/offer). Here is my table.

User table:

Id,  Want1, Want2, Offer1, Offer2, Type, uid, W/O
---  -----  -----  ------  ------  ----  ---  ---
1      a     null   null    null    KK    5    W
2    null      b    null    null    LL    30   W
3    null    null     a     null    KK    20   O
4    null    null   null      b     LL    13   O
5    null    null     a     null    BB    6    O
6    null    null     a     null    KK    70   O

Desired table result:

Want1, Want2, Offer1, Offer2, Type, uidW, uidO
-----  -----  ------  ------  ----  ----  ----
 a      null     a     null    KK     5    20
 null     b     null     b      LL    30    13
 a      null     a     null    KK     5    70

Is it possible to use pure SQL to get this result?

Answer

Here is a solution

SELECT w.want1,
       w.want2,
       o.offer1,
       o.offer2,
       w.Type,
       w.uid AS uidW,
       o.uid AS uidO
  FROM my_table w LEFT JOIN
       my_table o ON (IFNULL(w.want1, 'x') = IFNULL(o.offer1, 'x') AND
                       IFNULL(w.want2, 'x') = IFNULL(o.offer2, 'x') AND
                       w.type = o.type)
 WHERE w.wo = 'W' AND
       o.wo = 'O';

Output

want1   want2   offer1  offer2  Type    uidW    uidO
a   (null)  a   (null)  KK  5   20
(null)  b   (null)  b   LL  30  13
a   (null)  a   (null)  KK  5   70

Demo Here – http://sqlfiddle.com/#!9/e1ff2e/20

Leave a Reply

Your email address will not be published. Required fields are marked *