2

I am trying to get the result of a record which has a missing entry in the other table. I already tried using NOT IN and NOT EXIST but I cannot get the desired result. The output should be Kevin and US Visa since "Kevin" has no US Visa on the table tbl_personnel_documents but I always get an empty row. It should be like this:

name  | documentName  |  
----------------------
Kevin   US Visa

Note: I am trying to get the result by using only the vessel_id and lastJoinedVsl which in this case is id number 4.

Here's my code and sample data: http://sqlfiddle.com/#!9/46790e/1

Please help. Many Thanks

  • Don't use LEFT when you don't need it. – Rick James Mar 13 '18 at 18:27
0

So I think what your looking for is this:

I used this to find the abandoned the abandoned records:

SELECT tpd.id, tpi.name, td.id, td.documentName
FROM tbl_personnel_documents AS tpd
LEFT JOIN tbl_personnel_info AS tpi ON tpi.id = tpd.personnel_id
RIGHT JOIN tbl_documents AS td ON td.id = tpd.document_id
;

Returns

TPD_ID, TPI_NAME, TD_ID,     TD_DOCNAME
-------------------------------------------
   '1',  'Kevin',   '1',    'PASSPORT'
   '2',  'Kevin',   '2',    'AUSTRALIAN VISA'
  NULL,     NULL,   '3',    'US VISA'

The reason you will not get the result

name  | documentName  |  
----------------------
Kevin   US Visa

Is because there is no value linking them.

Let me know if this is what you are looking for.

😁

EDIT 1:

If you want to filter them out you can use an INNER JOIN on your joins.

That will strip out all values that don't have a linking value, based on the query in your SQL Fiddle:

SELECT tpd.id, tpi.name, td.id, td.documentName
FROM tbl_personnel_documents AS tpd
INNER JOIN tbl_personnel_info AS tpi ON tpi.id = tpd.personnel_id
INNER JOIN tbl_documents AS td ON td.id = tpd.document_id
;

If you want to see only the missing records you can use:

SELECT tpd.id, tpi.name, td.id, td.documentName
FROM tbl_personnel_documents AS tpd
LEFT JOIN tbl_personnel_info AS tpi ON tpi.id = tpd.personnel_id
RIGHT JOIN tbl_documents AS td ON td.id = tpd.document_id
WHERE tpd.id IS NULL;
| improve this answer | |
  • Thanks for answering, so does it mean that I won't be able to filter out to get the missing value? There's no other way? – Cloud Strifez Mar 13 '18 at 6:15
  • FYI - if I added an edit to showcase this for you. – JayRizzo Apr 17 '18 at 5:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.