How to display with MySQL INNER JOIN with possible empty fields

I am trying to display empty fields using MySQL SELECT and INNER JOIN

Below are the attributes for Table 1 (tbl_request)

requestID, requestTitle, requestRemarks

Below are the attributes for Table 2 (tbl_event)

eventID, status, image, requestID

status is either 1(approved) or 2(declined)

If I want to display if it is approved, the query would be

SELECT * from (tbl_event INNER JOIN tbl_request on tbl_event.requestID = tbl_request.requestID) where tbl_event.status = 1;

If I want to display list of events that have not been approved/declined yet (meaning not existed), what would be the query?

The result I’m trying to get is:

requestID      requestTitle      requestRemarks      eventID      status      image      
    5           Volleyball          sport1
    9           Swimming            sport2         
    11          Hiking              sport3

Answer

You could make it an outer join and then check that there is no record for the outer joined table

SELECT * 
FROM      tbl_request
LEFT JOIN tbl_event 
       ON tbl_event.requestID = tbl_request.requestID
WHERE     tbl_event.requestID IS NULL;

Leave a Reply

Your email address will not be published. Required fields are marked *