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
