CODEWITHSUNDEEP
mysqlsqljoin
amine
amine

Reputation: 479

MYSQL join without using primary key

I wonder if MYSQL can do something as follow:

 **Table 1**
    |name|salary|seniorLevel|
    |Angela|3500||
    |Cindy|4500||
    |Sam|||
    |Paul|8000||


    **Table2**
    |seniorLevel|salary_start|salary_end|
    |A|3001|4000|
    |B|4001|5000|
    |C|5001|6000|

Desired output:
    |Name|salary|seniorLevel|
    |Angela|3500|A|
    |Cindy|4500|B|
    |Sam|||
    |Paul|8000||

I managed to get the first two lines desired output (Angela & Cindy) using SQL below, but how can i twist my codes to outputting full set (including Sam & Paul) even they not meet criteria.

select Table1.name, Table1.salary, Table2.seniorLevel from Table1, Table2 where Table1.salary <= Table2.end and Table1.salary >= Table2.start;

Thanks for your help. :)

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I think you want a left join:

select t1.*, t2.seniorLevel
from table1 t1 left join
     table2 t2
     on t1.salary >= t2.salary_start and t1.salary <= t2.salary_end;

Upvotes: 3

Related Questions