conditionnal loops in mysql
Hi, I've got 2 tables in mysql, table 1 which contains records with fields like +ADSM in table 2, I got records like: +ADSM anr2343E +ADSM anr4523E +ADSM anr7823E now, i'd like to change each record in table1 where a field contains a +ADSM entry, lookup the corresponding records in table 2, and, in this case for example, add 3 records to table1 where the +ADSM field is replaced by anr2343e in record 1, anr4523e in record 2 and anr7823e in record 3. But i have no idea if this is possible in mysql, and if it is, how I should do it. can anyone help ? thanks, Tom. regards, 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
outer joins: tried some sql code myself but doesn't work - should be easy i think ?
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
outer joins
Hi, 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 ?). 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
