Re: multiple SELECT and missing rows.

This is best done with a LEFT JOIN. Please read up the manual about it. ----- Original Message ----- From: "Maxim Maletsky" <maxim.maletsky@japaninc.net> To: <mysql@lists.mysql.com>; "'PHP General List. (E-mail)'" <php-general@lists.php.net> Sent: Tuesday, April 03, 2001 9:41 Subject: SQL : multiple SELECT and missing rows. > > Hello Guys, > > I have a (very simple?) SQL question. I'm more then sure the answer to it is > whether "just do this..." or "It's impossible man, how could you even get > that into your mind..." > > > > I have this database design: (it's more complex of course, but you get an > idea) > > > > Table 'accounts': > +-------+------+------------+ > | ID | type | since | > +-------+------+------------+ > | 00001 | P | 1999-08-01 | > | 00002 | F | 1999-11-06 | > | 00003 | S | 2000-07-05 | > | 00004 | P | 2001-01-01 | > +-------+------+------------+ > > > Table 'addresses': > +-------+--------------+------+---------+ > | ID | city | pref | country | > +-------+--------------+------+---------+ > | 00001 | Shibuya-ku | 13 | JP | > | 00002 | Nakano-ku | 13 | JP | > | 00004 | Toyonaka-shi | 27 | JP | > +-------+--------------+------+---------+ > > > > As you can see, the main data is in 'accounts', the 'addresses.ID' is UNIQUE > and corresponds to 'accounts.ID'. The fact is that not every account has an > address registered with it. > > > > I need to list ALL the accounts in this order: > > +---+------+-----+--------+-------+ > | ID | Type | City | Country | Since | > +---+------+-----+--------+-------+ > > > > Therefore I am trying to do it with the following query: > > > > SELECT > accounts.ID AS ID, > accounts.type AS Type, > addresses.city AS City, > addresses.country AS Country, > accounts.since AS Since > FROM > accounts, > addresses > WHERE > addresses.ID=accounts.ID > ORDER > BY > accounts.ID > ASC > LIMIT > 10 > ; > > > And, this selects me IDs 00001, 00002 and 00004. > 00003, obviously, is not here because there's no such address.ID as stated > in my WHERE clause. > > > > Can anyone suggest me how do I SELECT ALL of the accounts having simply NULL > on 'addresses.*' when there's no such row, instead of 'loosing' the > whole > 'account' row? > > I sure know how to do it using two queries, still, I wonder if one single > query can do this job. > > Where do I miss the logic? > > > > Thanks in advance. > > -maxim > > > > > Maxim Maletsky - maxim@j-door.com > Webmaster, J-Door.com / J@pan Inc. > LINC Media, Inc. > TEL: 03-3499-2175 x 1271 > FAX: 03-3499-3109 > http://www.j-door.com > http://www.japaninc.net > http://www.lincmedia.co.jp > > --------------------------------------------------------------------- > 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 <mysql-thread70326@lists.mysql.com> > To unsubscribe, e-mail <mysql-unsubscribe-rhopkins=mail.waytech.com.tw@lists.mysql.com> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php