That rather depends on the way the database is designed and the results you want. SELECT a.name <http://a.name/>, a.address, b.postcode,c.country FROM (a LEFT JOIN b ON b.uid = a.b_uid ) LEFT JOIN c ON c.uid = b.c_uid would give you name, address, postcode & country columns from 3 tables You need the parentheses because you want to join a with b first, and then join c to ab a->b->c (for a->b and a->c, you wouldn't need them) so with a holding *
uid * * name * * address * * b_uid * 1 Albert Albert Avenue 1 2 Bob Robert Road 2 3 Carole Carole Close 0 and b holding * uid * * postcode * * c_uid * 1 AA1 1AA 1 2 BB2 2BB 0 and c holding * uid * * country * 1 Albania you would get * name * * address * * postcode * * country * Albert Albert Avenue AA1 1AA Albania Bob Robert Road BB2 2BB Carole Carole Close note the blank cells. This is because Access (in this case) defaults to outer joins An outer join is where the first table is read and the second table's data is added An inner join is where the data has to exist in both tables. If we run SELECT a.name <http://a.name/>, a.address, b.postcode,c.country FROM ( a INNER JOIN b ON b.uid = a.b_uid) LEFT JOIN c ON c.uid = b.c_uid then we get * name * * address * * postcode * * country * Albert Albert Avenue AA1 1AA Albania Bob Robert Road BB2 2BB because there is no postcode on record for Carol If we made the c table an inner join then we'd lose Bob, too BTW a LEFT outer join gets all the data from the first table (left - imagine two printouts in front of you) and adds what it can from the second table. If there's nothing in the 1st table which points to data in the second table, then it won't appear. If we had country 6 of Belgium, then it wouldn't appear unless something in table b had a c_uid of 6 and something in a pointed to the new record in b (following this?!) A RIGHT outer join is the opposite way round Ian On 10/20/05, Mukesh Kumar <[EMAIL PROTECTED]> wrote: > > Thank Ian, > Can you please further guide me what would be select sql if there are > more than 3 table > Thank you, > Mukesh Kumar > > ----- Original Message ----- > *From:* Ian Bambury <[EMAIL PROTECTED]> > *To:* Mukesh Kumar <[EMAIL PROTECTED]> > *Sent:* Wednesday, October 19, 2005 7:18 PM > *Subject:* Re: select Sql required > > You can't be running the page I sent you - none of these places are in > the data you sent to me > Try running JUST the code I sent and let me know what you get - I get: > 42 Eindhoven 14 416 14 eindhoven 352 177 Anjum 20 4736 40 > Dordrecht 13 384 13 dordrecht 320 39 Den Haag 12 352 12 denhaag > 288 38 > Den Bosch 11 320 11 denbosch 256 37 Breda 10 288 10 breda 224 > 36 Assen 9 256 9 assen 192 35 Arnhem 8 224 8 arnhem 160 34 > Amstelveen 5 192 5 amsterdam 96 33 > Amsterdam 5 160 5 amsterdam 96 32 Alkmaar 4 128 4 alkmaar 64 30 > Leiden 21 64 21 leiden 576 31 Apeldoorn 7 96 7 apeldoorn 128 51 > Middelburg 24 704 52 Nijmegen 25 736 53 Rotterdam 26 768 > 54 > Texel 27 800 55 Tilburg 28 832 56 Utrecht 29 864 57 > Venlo 30 896 58 > Vlissingen 31 928 59 Zwolle 32 960 60 Arum 20 992 > > On 19/10/05, Mukesh Kumar <[EMAIL PROTECTED] > wrote: > > > > Thanks a lot Ian, > > but even I'm useing your sql > > $result = mysql_query("SELECT * FROM location LEFT JOIN region ON > > location.nIdParent = region.nId ") or die("update error due to :" . > > mysql_error()); > > I got the following output > > ################ > > <table border="1" align="center" cellpadding="0" cellspacing="0"> > > <th>nId</th> > > <th>sName</th> > > <th>nIdParent</th> > > <th>nOrder</th> > > <th>nId</th> > > <th>sName</th> > > <th>nOrder</th> > > <tr> > > <td>23</td> > > <td>maastricht</td> > > <td>23</td> > > <td>640</td> > > </tr> > > <tr> > > <td>22</td> > > <td>lelystad</td> > > <td>22</td> > > <td>608</td> > > </tr> > > <tr> > > <td>20</td> > > <td>leeuwarden</td> > > <td>20</td> > > <td>544</td> > > </tr> > > <tr> > > <td>19</td> > > <td>hengelo</td> > > <td>19</td> > > <td>512</td> > > </tr> > > <tr> > > <td>18</td> > > <td>heerlen</td> > > <td>18</td> > > <td>480</td> > > </tr></table> > > ############ > > I don't know where is the problem > > Thank you with regards, > > Mukesh Kumar > > > > ----- Original Message ----- > > *From:* Ian Bambury <[EMAIL PROTECTED]> > > *To:* Mukesh Kumar <[EMAIL PROTECTED]> > > *Sent:* Wednesday, October 19, 2005 6:34 PM > > *Subject:* Re: select Sql required > > > > <html> > > <head> > > <title> > > > > Hi Mukesh - this definitely works on my machine > > > > </title> > > </head> > > > > <body> > > <?php > > mysql_query("DROP DATABASE IF EXISTS test") or die ("Can not drop test > > database - Error: ".mysql_error()); > > mysql_query("CREATE DATABASE test") or die ("Can not create test > > database - Error: ".mysql_error()); > > mysql_query("USE test") or die("<br />Could not use test database - > > Error: " . mysql_error()); > > mysql_query("CREATE TABLE location ( > > nId int(11) NOT NULL auto_increment, > > sName varchar(255) NOT NULL default '', > > nIdParent int(11) NOT NULL default '0', > > nOrder int(11) NOT NULL default '999999', > > PRIMARY KEY (nId) > > ) TYPE=MyISAM") or die ("Can not select due to ".mysql_error()); > > > > mysql_query("CREATE TABLE region ( > > nId int(11) NOT NULL auto_increment, > > sName varchar(255) NOT NULL default '', > > nOrder int(11) NOT NULL default ' 77799', > > PRIMARY KEY (nId) > > ) TYPE=MyISAM;") or die ("Can not select due to ".mysql_error()); > > > > mysql_query("INSERT INTO location VALUES (42, 'Eindhoven', 14, 416)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (177, 'Anjum', 20, 4736)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (40, 'Dordrecht', 13, 384)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (39, 'Den Haag', 12, 352)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (38, 'Den Bosch', 11, 320)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (37, 'Breda', 10, 288)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (36, 'Assen', 9, 256)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (35, 'Arnhem', 8, 224)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (34, 'Amstelveen', 5, 192)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (33, 'Amsterdam', 5, 160)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (32, 'Alkmaar', 4, 128)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (30, 'Leiden', 21, 64)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (31, 'Apeldoorn', 7, 96)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (51, 'Middelburg', 24, 704)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (52, 'Nijmegen', 25, 736)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (53, 'Rotterdam', 26, 768)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (54, 'Texel', 27, 800)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (55, 'Tilburg', 28, 832)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (56, 'Utrecht', 29, 864)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (57, 'Venlo', 30, 896)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (58, 'Vlissingen', 31, 928)")or > > die ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (59, 'Zwolle', 32, 960)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO location VALUES (60, 'Arum', 20, 992)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (21, 'leiden', 576)")or die ("Can > > not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (7, 'apeldoorn', 128)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (4, 'alkmaar', 64)")or die ("Can > > not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (5, 'amsterdam', 96)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (8, 'arnhem', 160)")or die ("Can > > not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (9, 'assen', 192)")or die ("Can > > not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (10, 'breda', 224)")or die ("Can > > not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (11, 'denbosch', 256)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (12, 'denhaag', 288)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (13, 'dordrecht', 320)")or die > > ("Can not add due to ".mysql_error()); > > mysql_query("INSERT INTO region VALUES (14, 'eindhoven', 352)")or die > > ("Can not add due to ".mysql_error()); > > //mysql_query("USE {$sys['db_name']}") or die("<br />Could not use > > database - Error: " . mysql_error()); > > $result=mysql_query("SELECT * FROM location LEFT JOIN region ON > > location.nIdParent = region.nId") or die("<br />Could not use database - > > Error: " . mysql_error()); > > > > $msg = '<table border="1">'; > > while ($row = mysql_fetch_array($result)) > > { > > $msg .= "<tr>"; > > for($i=0;$i<mysql_num_fields($result);$i++) > > { > > $msg .= "<td class='list'> {$row[$i]} </td>"; > > } > > $msg .= "</tr>"; > > } > > $msg .= '</table>'; > > echo $msg; > > > > ?> > > </body> > > </html> > > > > > [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/HKFolB/TM --------------------------------------------------------------------~-> Community email addresses: Post message: [email protected] Subscribe: [EMAIL PROTECTED] Unsubscribe: [EMAIL PROTECTED] List owner: [EMAIL PROTECTED] Shortcut URL to this page: http://groups.yahoo.com/group/php-list Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/php-list/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
