MySQL join two tables from two columns

Question

I'm going to join two table with two columns contain id from another table.

Table users

id | first_name | last_name |
---+------------+-----------+
 1 | John       | Doe       |
 2 | Jane       | Doe       |
 3 | Some       | Name      |

Table stamp

id |    date    | applicant_id  | app_by_id |
---+------------+---------------+-----------+
 1 | 2013-03-15 | 1             | 2         |
 2 | 2013-03-10 | 2             | 3         |
 3 | 2013-03-13 | 2             | 1         |

What I want to show:

    date    | applicant | app_by    |
------------+-----------+-----------+
 2013-03-15 | John Doe  | Jane Doe  |
 2013-03-10 | Jane Doe  | Some Name |
 2013-03-13 | Jane Doe  | John Doe  |

My query:

SELECT CONCAT_WS(' ', NULLIF(t1.first_name, ' '), NULLIF(t1.last_name, ' ')) AS applicant,
    CONCAT_WS(' ', NULLIF(t1.first_name, ' '), NULLIF(t1.last_name, ' ')) AS app_by,
    t2.date
    FROM users t1
    INNER JOIN stamp t2 ON applicant_id = t1.id

I know there's something wrong with my query, but I don't know how to fix it.

user1159653 · Accepted Answer · 2013-03-22 05:26:47Z

0

You probably want to use LEFT JOIN so that you get values in each column even if the other join conditions fail. The following code also works with regular JOIN.

SELECT s.date,
CONCAT_WS(' ', NULLIF(u1.first_name, ' '), NULLIF(u1.last_name, ' ')) AS applicant,
CONCAT_WS(' ', NULLIF(u2.first_name, ' '), NULLIF(u2.last_name, ' ')) AS app_by
FROM stamp s
LEFT JOIN users u1 ON s.applicant_id = u1.id
LEFT JOIN users u2 ON s.app_by_id = u2.id

Proof: http://sqlfiddle.com/#!2/39aff/1/0

answered Mar 22, 2013 at 5:26

user1159653

Sign up to request clarification or add additional context in comments.

Comments

Adriaan Stander · Accepted Answer · 2013-03-22 04:58:19Z

0

Try your joins like

SELECT  CONCAT_WS(' ', NULLIF(u1.first_name, ' '), NULLIF(u1.last_name, ' ')) AS applicant,
        CONCAT_WS(' ', NULLIF(u2.first_name, ' '), NULLIF(u2.last_name, ' ')) AS app_by,
        s.date
FROM    stamp s INNER JOIN
        user u1 ON  s.applicant_id = u1.id INNER JOIN
        user u2 ON  s.app_by_id = u1.id

answered Mar 22, 2013 at 4:58

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Comments

Ravindra Gullapalli · Accepted Answer · 2013-03-22 05:01:02Z

0

Instead of joins, try to use sub query like this.

SELECT date, 
(SELECT CONCAT_WS(' ', NULLIF(first_name, ' '), NULLIF.last_name, ' ')) from USERS WHERE id = s.applicant_id) as applicant, 
(SELECT CONCAT_WS(' ', NULLIF(first_name, ' '), NULLIF.last_name, ' ')) from USERS WHERE id = s.app_by_id) as app_by 
from stamp s

answered Mar 22, 2013 at 5:01

Ravindra Gullapalli

9,1983 gold badges51 silver badges73 bronze badges

Collectives™ on Stack Overflow

MySQL join two tables from two columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related