[Resolved] [Resolved] Compairing tables

[jrmontg]

I have two tables

CompanyAddress

AddressKey
CustNum
Address1...etc

Meetingattendees

MeetingKey
CustNum
etc....

I need to find who in MeetingAttendees does not have a Address registered with us.

All I can get is all the attendees who do have a address, and when I do WHERE CompanyAddress.CustNum <> Meetingattendees.CustNum I seem to get all records from companyAddress. But I do need all of them just the ones in Meetingattendees that don't have a record in CompanyAddress.

Confused?

Thanks
Jeff

[drawmack]

Originally posted by jrmontg

CompanyAddress

AddressKey
CustNum
Address1...etc

Meetingattendees

MeetingKey
CustNum
etc....

SELECT * FROM Meetingattendees as a WHERE CustNum NOT IN SELECT CustNum FROM CompanyAddress

I think this will work, I've never personally used select statements in where clauses in mysql, but I'm pretty sure you can.

[ZibingsCoder]

Well, it only works in...I believe >4.1, but don't quote me on that. Basically, subqueries are brand new in MySQL.

[jrmontg]

I get a error. I guess that I am using a version < 4.1

[tsinka]

Hi,

try

SELECT a.* FROM Meetingattendees as a LEFT JOIN CompanyAddress as b ON a.CustNum=b.CustNum WHERE b.CustNum IS NULL

Thomas

[jrmontg]

That;s it, thanks