You want to use the "LEFT JOIN" syntax. A general outline would be:
SELECT [your fields]
FROM accounts LEFT JOIN addresses ON addresses.ID = accounts.ID
WHERE ...
ORDER ...
LIMIT ...
This would select all addresses.ID's, including those where accounts.ID is
NULL.
-Kevin
--On Tuesday, April 03, 2001 10:41 AM +0900 Maxim Maletsky
<[EMAIL PROTECTED]> wrote:
>
> 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 - [EMAIL PROTECTED]
> 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 <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]
> om> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
====================================================================
Kevin C. Miller - Carnegie Mellon University -
[EMAIL PROTECTED] - School of Computer Science - 412.512.3144
--------------------------------------------------------------------
---------------------------------------------------------------------
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
