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 | +---+--++ | 1 | P| 1999-08-01 | | 2 | F| 1999-11-06 | | 3 | S| 2000-07-05 | | 4 | P| 2001-01-01 | +---+--++ Table 'addresses': +---+--+--+-+ | ID| city | pref | country | +---+--+--+-+ | 1 | Shibuya-ku | 13 | JP | | 2 | Nakano-ku| 13 | JP | | 4 | 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 1, 2 and 4. 3, 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
Re: [PHP] SQL : multiple SELECT and missing rows.
"Maxim Maletsky" [EMAIL PROTECTED] wrote: 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. snip FROM accounts, addresses You're doing a straight join. You need to use a LEFT JOIN to return all the records from one table and those from a second table that match based on a common field. Read about joins in the manual and find a resource on SQL statements - it'll pay off. SELECT * FROM accounts LEFT JOIN addresses ON addresses.ID = accounts.ID 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? Use the query above and add: WHERE addresses.ID IS NULL -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.com/ - 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
Re: SQL : multiple SELECT and missing rows.
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 | +---+--++ | 1 | P| 1999-08-01 | | 2 | F| 1999-11-06 | | 3 | S| 2000-07-05 | | 4 | P| 2001-01-01 | +---+--++ Table 'addresses': +---+--+--+-+ | ID| city | pref | country | +---+--+--+-+ | 1 | Shibuya-ku | 13 | JP | | 2 | Nakano-ku| 13 | JP | | 4 | 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.IDAS 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 1, 2 and 4. 3, 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
