Sql statement problem

I need to write a sql statement that will retrieve records from one table and add a boolean field if the record customerid exists in a second table.  Example:
Table1 fields     CustomerID,CustomerName       Table 2 field   CustomerID

I want the sql statement to be something like:  
Select table1.CustomerID, table1.CustomerName, table1.IsInTable2
From Customer InnerJoin Table2 on table2.CustomerID

Thats pretty much guess work on my part but I hope it conveys enough information.
                                       
 
LVL 2
Moe DeShongAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, please try this alternative:
Select table1.CustomerID, table1.CustomerName, IIF(ISNULL(table2.CustomerID),0,1) IsinTable2
From Customer left Join Table2 on table2.CustomerID

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
close:
Select table1.CustomerID, table1.CustomerName, case when table2.CustomerID IS NULL then 0 else 1 end IsinTable2
From Customer left Join Table2 on table2.CustomerID

Open in new window

0
 
Moe DeShongAuthor Commented:
Here's the way I input but it gives an error

            cSql = "SELECT *, CASE when ContractInformation.txtCustomerID IS NULL then 0 else 1 END HasContract " _
              & "FROM Customers LEFT JOIN ContractInformation ON ContractInformation.txtCustomerID  " _
              & "ORDER BY datInputDate Desc"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what error?
0
 
SharathData EngineerCommented:
check this:
  cSql = "SELECT C.*, CASE when I.txtCustomerID IS NULL then 0 else 1 END HasContract " _
              & "FROM Customers C LEFT JOIN ContractInformation I ON C.CustomerID = I.txtCustomerID  "  
0
 
Moe DeShongAuthor Commented:
The error is "EerrorInfo.getdescription failed"   Error code -2147467259

I am using an MS Access backend if that makes a difference.
0
 
Moe DeShongAuthor Commented:
Perfect.  Glad to see someone else is working on Saturday.   Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.