Hi, did you get any answer ? I'm having the same problem. If i wanted all results from table1, even if they don't appear in table2 i would do like:
SELECT ..... FROM table1 LEFT JOIN table2 ON (table1.id=table2.id) ... But how to join SELECT ..... FROM table1 LEFT JOIN table2 ON (table1.id=table2.id) ... and SELECT ..... FROM table2 LEFT JOIN table2 ON (table1.id=table2.id) ... ??? I have 2 solutions and don't like any of them. 1st SELECT ... FROM table3 LEFT JOIN table1 ON (table3.id=table1.id ..) LEFT JOIN table2 ON (table3.id=table2.id ..) but, obviously, this doesn work in cases of counts and sums.. (because table2 and table3 weren joined very well). 2nd Creating one temporary table with all keys that i want and with data table1. Idem for table2. Then, joining those 2 temporary tables will simple join will give me what i want. I simply don't like any of this solutions. Did you find a better solution ? Thanx in advance, On Thu, 2002-03-21 at 07:08, Van Overbeke, Tom wrote: > 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] -- dsoares (sql) --------------------------------------------------------------------- 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
