MySQL multi column match with multi row results Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of MySQL multi column match with multi row results without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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

We are here to answer your question about MySQL multi column match with multi row results - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji