-1

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
tipah
  • 27
  • 6

1 Answers1

-1

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;
trincot
  • 317,000
  • 35
  • 244
  • 286
  • 1
    this is exactly what im looking for. thank you! – tipah Mar 06 '21 at 10:06
  • I am trying to pass the data to next page I used "SELECT * FROM tbl_request LEFT JOIN tbl_event ON tbl_event.requestID = tbl_request.requestID WHERE tbl_event.requestID = $requestID" but there is an error. Can you point out what I am missing? – tipah Mar 06 '21 at 10:21
  • And what is the error? Also, you should keep the `WHERE` condition as I put here. You can *add* to it `AND tbl_request.requestID = $requestID`. Note that you must compare with the `tbl_request` table, because there is no matching record in the other table. – trincot Mar 06 '21 at 10:50
  • the error is "mysqli_fetch_array() expects parameter 1 to be mysqli_result, bool given" – tipah Mar 06 '21 at 16:16
  • That means you have a syntax error in your sql. Double check. – trincot Mar 06 '21 at 16:34
  • In PHP you can print the deeper error message. See [this answer](https://stackoverflow.com/a/15440076/5459839) – trincot Mar 06 '21 at 16:41