This is best done with a LEFT JOIN. Please read up the manual about it.
----- Original Message -----
From: "Maxim Maletsky" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "'PHP General List. (E-mail)'"
<[EMAIL PROTECTED]>
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 - [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]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
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
