Nested SELECT Statement in Where Clause

[nickg21]

Hey everyone,
     I am looking to write a query that I think can be solved a nested select but I am not yet that fluent in MySQL to be able to get the query working correctly.  
Basically what I want to do is this; 

SELECT count(*) from Inquiry 
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
WHERE Inquirer.Program = 'value' AND Inquiry.ID 
does not exist in (SELECT TransferInquiryID_fk from Transfer)  

as you can see I want all results where the Inquiry.ID of the record is not contained in the list of TransferInquiryID's
If anyone could help me out with this that would be great.
Thank you very much,
NickG

[nickg21]

sorry for the formatting issues it took me 15 minutes just to get this to post, I don't understand why DaniWeb made such a ridiculous way to try to post code and queries.

[simplypixie]

Formatting on here is no different to any other forum, just type in the box and when you want to display code, hit the Code link, put in the code and click the Insert Code button.

As for your problem, try this:

SELECT count(*) from Inquiry 
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
WHERE Inquirer.Program = 'value' AND Inquiry.ID 
not in (SELECT TransferInquiryID_fk from Transfer)

[BitBlt]

You might also consider left-joining to transfer. It tends to perform better:

SELECT count(*) from Inquiry 
Inner Join Inquirer 
on Inquirer.ID = Inquiry.InquirerID_fk
left join Transfer 
on Inquiry.ID = Transfer.TransferInquiryId_fk
WHERE Inquirer.Program = 'value' 
AND TransferInquiryID_fk IS NULL

Generally speaking, your query has to resolve the sub-select for each row (very slow) as opposed to joining (much faster). Then, by virtue of the joined column coming back NULL, you establish that the ID doesn't exist there. Voila!

Hope that helps! Good luck!