We help IT Professionals succeed at work.

Not In

pdvsa
pdvsa asked
on
Experts, I need to know if the bold part below means that there would be a hit if the tblLetterOfCredit.LCNo is not IN import-jpm.  Is that what it is saying or is it the other way around?

Do I need to have quotes around something because the LCNo is text?  
I want to return hits that are not IN import-jpm.  Meaning that if there is a LCNo in tblLetterOfCredit and there is no match on import-JPM then show that one.  The BankID = 1 is not part of the issue.  

thank you

SELECT tblLetterOfCredit.EndUserID, tblBanks_Participating.BankID, tblLetterOfCredit.LCNo, tblLetterOfCredit.Amount, tblLetterOfCredit.ExpiredYN, tblLetterOfCredit.CanceledDate, tblLetterOfCredit.DateOfIssueSB
FROM tblLetterOfCredit LEFT JOIN tblBanks_Participating ON tblLetterOfCredit.LetterOfCreditID = tblBanks_Participating.LCID
WHERE (((tblBanks_Participating.BankID)=1) AND ((tblLetterOfCredit.LCNo) Not In (select [JPM Ref Number] from [Import-JPM])));
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2015
Commented:
if the bold part below means that there would be a hit if the tblLetterOfCredit.LCNo is not IN import-jpm.
Correct.

Do I need to have quotes around something because the LCNo is text?
No. You only need quotes around text literals. If you are selecting a text column, then the column name is what you enter, sans quotes.
pdvsaProject finance

Author

Commented:
ok thank you.  It is not giving me the results I need for some reason but I will figure it out.  

Woudl it still work if it was the other way around meaning if the import-JPM.JPM Ref No was not IN tblLetterOfCREdit.LCNo?

thank you
Most Valuable Expert 2011
Top Expert 2015

Commented:
Woudl it still work if it was the other way around meaning if the import-JPM.JPM Ref No was not IN tblLetterOfCREdit.LCNo?
I don't believe so. Here's a simplified example:

Table1
data1
=====
1
2
3
4
5
6

Table2
data2
=====
2
4
6

Running this query:

SELECT * FROM table1 WHERE data1 NOT IN (SELECT data2 FROM table2)

Open in new window


would give:

1
3
5

Running the flip of that:

SELECT * FROM table2 WHERE data2 NOT IN (SELECT data1 FROM table1)

Open in new window


would give no results, because all of the data in the data2 column of table2 can be found in the data1 column of table1.
pdvsaProject finance

Author

Commented:
very good example.  thank you for that....