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
