IF using inner join MySql?

How do I build a joint to select either table

Table jobs

Id    |    name    |   salary   |   company   |  type
1     |    php     |   17.850   |   5         |  1
2     |    mysql   |   4.500    |   89        |  2
2     |    nodejs  |   7.500    |   89        |  1

Table Company

Id    |    name    |   Area     |   status   
1     |    Facebook|   Developer|   1
2     |    Google  |   Manager  |   1

Table Candidate

Id    |    name         |   City     |   phone   
1     |    Alan Kout    |   Nevada   |   1 555 6666
2     |    Wagner Mom   |   L.A.     |   1 444 8965

My query mysql, inner join candidate or company

If type == 1 in table jobs INNER JOIN ON table company

If type == 2 in table jobs INNER JOIN ON table candidate

Example

SELECT * FROM table_jobs
IF(table_jobs.type == 1, INNER JOIN table_company, INNER JOIN table_candidate)

This is possible?

Solution

You can achieve this using a LEFT JOIN instead of an INNER JOIN:

SELECT  *
  FROM  table_jobs tj
    LEFT JOIN table_company tco ON tj.type = 1 AND tco.id = tj.id
    LEFT JOIN table_candidate tca ON tj.type = 2 AND tca.id = tj.id

This will join to table_company where the type is 1, and table_candidate where the type is 2.

You can then SELECT whichever columns are needed from each table as appropriate.