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
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