I have these tables: table fields a IPadd, MAC u IPadd, MAC, user si IPadd, deviceID, ifIndex sf deviceID, ifIndex, MAC For any given values of IPadd and MAC in 'a' there may or may not be records in 'u', 'si' and 'sf' For each IPadd and MAC in a I want to find the corresponding 'user' in 'u' (or null if there is none), so I do a left join: SELECT a.IPadd, a.MAC, u.user FROM a LEFT JOIN u ON a.IPadd=u.IPadd AND a.MAC=u.MAC I also want corresponding records from si and sf where a.IPadd=si.IPadd AND si.deviceID=sf.deviceID AND si.ifINdex=sf.ifIndex AND a.MAC=sf.MAC If I simply left join tables si and sf: SELECT a.IPadd, a.MAC, u.user, si.deviceID FROM a LEFT JOIN u ON a.IPadd=u.IPadd AND a.MAC=u.MAC LEFT JOIN si ON a.IPadd=si.IPadd LEFT JOIN sf ON a.MAC=sf.MAC and add: WHERE si.deviceID=sf.deviceID AND si.ifINdex=sf.ifIndex then I don't get any records from 'a' for which there are no records in si and sf; on the other hand if I put the extra conditions in the last 'ON': SELECT a.IPadd, a.MAC, u.user si.deviceID FROM a LEFT JOIN u ON a.IPadd=u.IPadd AND a.MAC=u.MAC LEFT JOIN si ON a.IPadd=si.IPadd LEFT JOIN sf ON a.MAC=sf.MAC AND si.deviceID=sf.deviceID AND si.ifINdex=sf.ifIndex then I get records from 'si' and 'sf' which don't match the 'IPadd' and 'MAC' from 'a'. How can I construct a SELECT to do what I want? regards, -- John Stumbles [EMAIL PROTECTED] I.T. Services Centre, University of Reading http://www.rdg.ac.uk/~visstmbl +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ never generalise --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php