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

You shouldn't use `IFNULL()` if you want to get the actual null values. That's used to replace `NULL` with a default value. — Barmar, Aug 23 '23 at 16:22

Jon White · Accepted Answer · 2023-08-25T11:43:44.867

0

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

edited Aug 25 '23 at 11:43

answered Aug 23 '23 at 16:52

Jon White

682
4
12

Thankyou Jon, I made a silly mistake of not joining hostname in addition to pid, instead used in where clause. So i did not get the expected outcome. Anyways thanks much. This works. – gjvignesh Aug 24 '23 at 09:51
@gjvignesh I'm glad I could help. Another user mentioned in the comments that sharing your attempted query would help us see where you had gone wrong, someone would have spotted that mistake instantly on here. Great that it's all working now – Jon White Aug 24 '23 at 10:50
@JonWhite. You are missing the `FROM`-clause from the query – slaakso Aug 25 '23 at 11:03
@slaakso right you are. Edited to fix. Fortunately the OP understood where I was going with it – Jon White Aug 25 '23 at 11:44

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

1 Answers1