Hi, I sent a message to the list earlier today about a problem with an outer join. I tried some stuff myself and checked the archives, but no message described my syntax problem with mysql, so I turn to the list again for some helpful soul to help me:
I have two tables that i want to link via an outer join: mysql> desc active_monitors; +----------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+----------+------+-----+---------+-------+ | monitor_id | int(7) | | PRI | 0 | | | endpoint | char(16) | | PRI | | | | monitor | char(75) | YES | | NULL | | | last_value | char(10) | YES | | NULL | | | last_update | datetime | YES | | NULL | | | sentry_profile | char(80) | | PRI | | | | status | char(10) | YES | | NULL | | +----------------+----------+------+-----+---------+-------+ and mysql> desc endpoints_defined_monitors; +----------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+----------+------+-----+---------+-------+ | monitor_id | int(7) | | PRI | 0 | | | monitor | char(75) | YES | | NULL | | | endpoint | char(16) | | PRI | | | | sentry_profile | char(80) | | PRI | | | +----------------+----------+------+-----+---------+-------+ i link these tables with the following sql statement: select D.monitor, D.endpoint, A.monitor from endpoints_defined_monitors D, active_monitors A where A.monitor_id = D.monitor_ID and A.endpoint = D.endpoint and A.sentry_profile = D.sentry_profile however, in some cases, there is a record in endpoints_defined_monitors that doesn't exist in active_monitors, or maybe even the other way round. Can someone tell me how I can adapt my sql statement so that I also get these records (with the field 'monitor' having a NULL value ?). The problem is that I need 3 where clauses between these 2 tables before I can link the records together. I came up with the following sql statement, but I still don't see any records that are missing in table active_monitors, I assume i am abusing the 'join syntax' but I don't know how to fix it: select D.monitor, D.endpoint ,A.monitor from endpoints_defined_monitors D left outer join active_monitors A on A.monitor_id = D.monitor_ID and A.endpoint = D.endpoint and A.sentry_profile = D.sentry_profile Thanks, Tom. Tom Van Overbeke Atos Origin Managed Services Unix/WAN/Tivoli Minervastraat 7 B-1930 Zaventem Tel. : +32 (2) 712 2650 Fax : +32 (2) 712 2622 E-mail : [EMAIL PROTECTED] --------------------------------------------------------------------- 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
