Can I use ifnull() in On clause of inner join?

Question

I have 2 columns in Table1: email1 and email2. I need to fetch full name by concatenation of FirstName and LastName which are in some other table and it is associated with the above mentioned email fields. But I need to make sure, if email2 field is there then fetch the associated Name columns, if email2 field is null then fetch values associated with email1. So please advise how I can use ifnull() and to do the inner join? Here is the table structure:

Table1

• email1
• email2

Table2

• FirstName
• LastName
• email

Thanks in advance, Krishna

Answer 1

SELECT  COALESCE(CONCAT(c.FirstName, ' ', c.LastName), CONCAT(b.FirstName, ' ', b.LastName)) FullName
FROM    table1 a
        LEFT JOIN table2 b
            ON a.email1 = b.email
        LEFT JOIN table2 c
            ON a.email2 = c.email

• SQLFiddle Demo

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins