Select records from one table that does not exist in another table

[aga2957]

Hi Guys!

Need help in putting up SQL string.

I have two tables. The first one contains customer information. Primary key is custno. The second one contains customer logins, primary key is also custno.

What I want to do is to view customer information that does not have customer logins.

Is it possible to do in MS Access 2003?

Regards,
Aga

 

[kalebson]

Create the query Join at custno and then put IS NULL in the criteria section OR

like this in the sql window HAVING (((customer logins) Is Null))

 

[aga2957]

Create the query Join at custno and then put IS NULL in the criteria section OR

like this in the sql window HAVING (((customer logins) Is Null))

Hi!

Thanks for the quick reply? Can you give me a sample code? Thanks !

 

[KeithG]

You will want to use a LEFT OUTER join from Cust Info table to the Customer Login table on the PK

 

[aga2957]

You will want to use a LEFT OUTER join from Cust Info table to the Customer Login table on the PK

Got It!

Thanks a bunch!

 

[kalebson]

kinda like this youll have to fiull in table names:

SELECT YourTable1.Custno,YourTable2.[customer Logins]
FROM YourTable1 INNER JOIN YourTable2 ON YourTable1.custno = YourTable2.custno
GROUP BY YourTable1.custno
HAVING (((YourTable2.[customer logins]) Is Null));

or

SELECT YourTable1.Custno
FROM YourTable1 INNER JOIN YourTable2 ON YourTable1.custno = YourTable2.custno
WHERE (((YourTable2.[customer logins]) Is Null))
GROUP BY YourTable1.custno;

 

[kalebson]

Oh ok I was under the assumption that the custno would be in both tables so LEFT OUTER would have still had the same results.

 

[aga2957]

Thanks for replying guys!