mysql how to select from one table if the second table has no matching information

Question

I am trying to only show results from table inventoryfolders if the id does not exist in table newlisting.

Here is my query:

$sql = "SELECT * 
          FROM avirobust.inventoryfolders  
         WHERE parentFolderID = '".$foname."' 
           AND agentID = '".$mne."' 
           AND parentFolderID NOT IN (SELECT folderID FROM marketplace.newlisting)";

However it never returns a result even if newlisting has no data in it.

I know that join can work but can I use something like in my query above? If so what did I do wrong in my query?

you need to use your programming language for conditionals and check to see if the result does not return grater than 0. — Amir Hassan Azimi, Oct 16 '15 at 19:33
Your query should work as you've written it. Can you post some sample data and the result you expect to get? — Barmar, Oct 16 '15 at 19:41
this part of the code is not working AND parentFolderID NOT IN (SELECT folderID FROM marketplace.newlisting)"; — Kristopher Therrien, Oct 16 '15 at 19:56

score 1 · Accepted Answer · answered Oct 16 '15 at 20:05

You can try use this code

SELECT inventoryfolders.* FROM avirobust.inventoryfolders LEFT JOIN marketplace.newlisting on (inventoryfolders.parentFolderID = newlisting.folderID) WHERE parentFolderID = '".$foname."' AND agentID = '".$mne."' AND newlisting.folderID is NULL

It will return all records from inventoryfolders where no corresponding rows in newlisting

mysql how to select from one table if the second table has no matching information

1 Answers1