PHP Get data from tables that do not have a specific relationship

Question

I have two tables (CONTROL & PAYMENT) that looks like this:

          CONTROL                                       PAYMENT
+--------------------------+                   +------------------------+
|  CNUMBER    |    SERIAL  |                   |  NUMBER    |   STATUS  |
+--------------------------+                   +------------------------+
| C-200-1     |  SUDU-03   |                   |    200     |    PAID   |
| C-201-1     |  SUDU-03   |                   |    201     |    PAID   |
| C-202-1     |  SUDU-03   |                   |    202     | PROCESSING|
| C-203-1     |  SUDU-03   |                   |    203     |    PAID   |
| C-204-1     |  SUDU-03   |                   |    204     | PROCESSING|
| C-204-1     |  SUDU-03   |                   |    205     | PROCESSING|
+--------------------------+                   +------------------------+

I want to show a list like this:

+--------------------------+
|  CNUMBER    |    STATUS  |
+--------------------------+
| C-200-1     |    PAID    |
| C-201-1     |    PAID    |
| C-202-1     | PROCESSING |
| C-203-1     |    PAID    |
| C-204-1     | PROCESSING |
| C-205-1     | PROCESSING |
+--------------------------+

So, I need get data (SELECT) from table "control" using "serial" (in control table) like a search criteria to find the "status" (in payment table) through "number" (in payment table) using %LIKE% (to match "number" and "cnumber")

I am trying but still I can't figure it out! I hope someone can help me and give me any idea.

EDIT:

SOLUTION 1:

---

select c.CNUMBER,p.STATUS from CONTROL c inner join PAYMENT p on substr(c.CNUMBER,3,3)=p.NUMBER;

---

Thanks to @Syscall I solved the main problem but I just have a little issue:

How I can show all the values even payment entries are null/empty ? If the entry on 'control' exist but in 'payment' not

something like that:

+--------------------------+
|  CNUMBER    |    STATUS  |
+--------------------------+
| C-200-1     |    PAID    |
| C-201-1     |            | <--empty
| C-202-1     | PROCESSING |
| C-203-1     |    PAID    |
| C-204-1     |            | <--empty
| C-205-1     | PROCESSING |
+--------------------------+

EDIT 2:

SOLVED, USING LEFT JOIN Thanks again @Syscall.

SOLUTION:

---

select c.CNUMBER,p.STATUS from CONTROL c left join PAYMENT p on substr(c.CNUMBER,3,3)=p.NUMBER WHERE col1 = '$var'

---

Answer 1

You could INNER JOIN using SUBSTR :

select c.CNUMBER,p.STATUS from CONTROL c
inner join PAYMENT p on substr(c.CNUMBER,3,3)=p.NUMBER;

Outputs :

+---------+------------+
| CNUMBER | STATUS     |
+---------+------------+
| C-200-1 | PAID       |
| C-201-1 | PAID       |
| C-202-1 | PROCESSING |
| C-203-1 | PAID       |
| C-204-1 | PROCESSING |
| C-204-1 | PROCESSING |
+---------+------------+