Path: csiph.com!x330-a1.tempe.blueboxinc.net!feeder1.hal-mli.net!border3.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!novia!newspump.sol.net!news-out.readnews.com!transit3.readnews.com!news-out.news.tds.net!newsreading01.news.tds.net!86597e80!not-for-mail From: "David Kerber" Subject: Re: Joining(?) question Message-ID: X-Comment-To: comp.lang.java.databases Newsgroups: comp.lang.java.databases In-Reply-To: <72ed2328-62ae-491f-be9f-b6faad045e13@q26g2000prq.googlegroups.com References: <72ed2328-62ae-491f-be9f-b6faad045e13@q26g2000prq.googlegroups.com Content-Type: text/plain; charset=IBM437 Content-Transfer-Encoding: 8bit X-Gateway: time.synchro.net [Synchronet 3.15a-Win32 NewsLink 1.92] Lines: 56 Date: Wed, 27 Apr 2011 15:23:06 GMT NNTP-Posting-Host: 96.60.20.240 X-Complaints-To: news@tds.net X-Trace: newsreading01.news.tds.net 1303917786 96.60.20.240 (Wed, 27 Apr 2011 10:23:06 CDT) NNTP-Posting-Date: Wed, 27 Apr 2011 10:23:06 CDT Organization: TDS.net Xref: x330-a1.tempe.blueboxinc.net comp.lang.java.databases:331 To: comp.lang.java.databases In article <72ed2328-62ae-491f-be9f-b6faad045e13 @q26g2000prq.googlegroups.com>, firstsql@ix.netcom.com says... > On Nov 1, 5:17=A0pm, Lew wrote: > > Lee Fesperman wrote: > > > On Oct 30, 5:27 am, Lew wrote: > > >> mdR wrote: > > >>> SELECT BM.PARENT, BM.ITEM, IM.DESC > > >>> FROM BM > > >>> JOIN IM ON IM.ITEM =3D BM.ITEM > > >>> WHERE BM.PARENT =3D 1; > > >> Different RDBMSes vary wrt JOIN syntax. =A0But RGB's version should = have worked, > > >> too. > > > > >> Some allow > > > > >> SELECT BM.PARENT, BM.ITEM, IM.DESC > > >> =A0 FROM BM JOIN IM > > >> =A0 WHERE BM.PARENT =3D 1; > > > > >> if there's a FK on the ITEM column to the other table. > > > > > I realize that DBMS support of Standard SQL is weak, but which ones > > > support those semantics? According to SQL92, an unqualified JOIN > > > (without NATURAL, ON or USING) is the same as CROSS JOIN (that is, a > > > Cartesian Product.) > > > > Oops, left out the word "NATURAL", as in "NATURAL JOIN". >=20 > That certainly makes a difference and will give the right results > (given the table structure shown by the OP.) NATURAL joins on common > column names between the two tables. >=20 > FK joining is non-standard and begs the question: Which FK for which > table is used? That syntax is only allowed if there is exactly one FK/PK combination=20 that would satisfy the join. Otherwise you get an error that says=20 something to the effect of "there is more than one way to join table1 to=20 table2" --=20 /~\ The ASCII \ / Ribbon Campaign X Against HTML / \ Email! Remove the ns_ from if replying by e-mail (but keep posts in the=20 newsgroups if possible). --- * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet! --- Synchronet 3.15a-Win32 NewsLink 1.92 Time Warp of the Future BBS - telnet://time.synchro.net:24