Single Mysql query to display multiple columns NULL if those values not present in another table

Question

I have two tables say PROJECT and POST_PROJECT joined by common column PID.

PROJECT(PID,HOSTNAME,STATUS)
POST_PROJECT(PID,HOSTNAME,POST_STATUS)

There are scenarios where Hostname record in PROJECT table does not exist in POST_PROJECT table like below,

PROJECT:

(1,'HOST1','SUCCESS'),(1,'HOST2','FAIL')

PID, HOSTNAME, STATUS
1    HOST1     SUCCESS
1    HOST2     FAIL

POST_PROJECT:
(1,'HOST1','FAIL')

PID, HOSTNAME, POST_STATUS
1    HOST1     FAIL

In this case, i need a single query where i have to display all columns of both tables with value as NULL if Hostname does not exist in POST_PROJECT table like below,

(1,'HOST1','SUCCESS',1,'HOST1','FAIL')
(1,'HOST2','FAIL',1,NULL,NULL)

PID, HOSTNAME, STATUS, POST HOSTNAME, POST_STATUS
1    HOST1     SUCCESS HOST1          FAIL
1    HOST2     FAIL    NULL           NULL

Expected outcome:

PID,PROJECT_HOSTNAME,PROJECT_STATUS,PID,POST_PROJECT_HOSTNAME,POST_PROJECT_STATUS
(1,'HOST1','SUCCESS',1,'HOST1','FAIL')
(1,'HOST2','FAIL',1,NULL,NULL)

PID, HOSTNAME, STATUS, POST HOSTNAME, POST_STATUS
1    HOST1     SUCCESS HOST1          FAIL
1    HOST2     FAIL    NULL           NULL

Actual outcome:

PID,PROJECT_HOSTNAME,PROJECT_STATUS,PID,POST_PROJECT_HOSTNAME,POST_PROJECT_STATUS
(1,'HOST1','SUCCESS',1,'HOST1','FAIL')
(1,'HOST2','FAIL',1,'HOST1','FAIL')

PID, HOSTNAME, STATUS, POST HOSTNAME, POST_STATUS
1    HOST1     SUCCESS HOST1          FAIL
1    HOST2     FAIL    HOST1          FAIL

Answer 1

As mentioned in the comments you need a LEFT JOIN

Left joins take ALL the records in the first table (on the left of the join) and join any matching records from the right table.

Your PID and HOSTNAME columns essentially make up your key so you'll need to include them both in the join conditions.

Also, make sure you column selects choose the data you want with the correct column names. Your post_project table's hostname needs an alias to get your desired result

SELECT project.pid,
    project.hostname,
    project.status,
    post_project.hostname as post_hostname,
    post_project.post_status
FROM project
LEFT JOIN post_project
    ON project.pid = post_project.pid
    AND project.hostname = post_project.hostname