Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #325 > unrolled thread

Re: Joining(?) question

Back to article view | Back to comp.lang.java.databases

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: Joining(?) question "mdR" <mdr@THRWHITE.remove-dii-this> - 2011-04-27 15:23 +0000
    Re: Joining(?) question "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:23 +0000
      Re: Joining(?) question "Lee Fesperman" <lee.fesperman@THRWHITE.remove-dii-this> - 2011-04-27 15:23 +0000
        Re: Joining(?) question "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:23 +0000
          Re: Joining(?) question "Lee Fesperman" <lee.fesperman@THRWHITE.remove-dii-this> - 2011-04-27 15:23 +0000

#325 — Re: Joining(?) question

  To: comp.lang.java.databases
On Oct 29, 3:27 pm, "John B. Matthews" <nos...@nospam.invalid> wrote:
> In article
> <887f7e3b-2efe-403a-9028-cfbdb0c88...@l62g2000hse.googlegroups.com>,
>
>
>
>  mdR <mr...@lakenet.com> wrote:
> > On Oct 29, 10:22 am, RedGrittyBrick <RedGrittyBr...@spamweary.invalid>
> > wrote:
> > > mdR wrote:
> [...]
> > > SELECT BM.PARENT, BM.ITEM, IM.DESC
> > >    FROM BM, IM
> > >    WHERE IM.ITEM = BM.ITEM
> > >    AND BM.PARENT = 1
> [...]
> > hmm... that didn't work. I'll look for a SQL group... thanks again
>
> create table bm (parent integer, item integer);
> insert into bm values(1,2);
> insert into bm values(1,3);
> insert into bm values(4,8);
> insert into bm values(4,9);
>
> create table im (item integer, desc varchar(10));
> insert into im values(1,'First');
> insert into im values(2,'Second');
> insert into im values(3,'Third');
>
> select bm.parent, bm.item, im.desc
>   from bm, im
>   where im.item = bm.item
>   and bm.parent = 1;
>
> Works with the Java database H2 <http://h2database.com/>. I'm not
> familiar with Derby, other than its Java heritage. Do you need an
> explicit JOIN clause?
>
> --
> John B. Matthews
> trashgod at gmail dot comhttp://home.roadrunner.com/~jbmatthews/

I was too quick on the previous reply... I simply pasted the reply
into the query.
yes, it works now. (sorry RGB)

SELECT BM.PARENT, BM.ITEM, IM.DESC
FROM BM
JOIN IM ON IM.ITEM = BM.ITEM
WHERE BM.PARENT = 1;

-mark

---
 * 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

[toc] | [next] | [standalone]

#326

  To: comp.lang.java.databases
mdR wrote:
> SELECT BM.PARENT, BM.ITEM, IM.DESC
> FROM BM
> JOIN IM ON IM.ITEM = BM.ITEM
> WHERE BM.PARENT = 1;

Different RDBMSes vary wrt JOIN syntax.  But RGB's version should have worked, 
too.

Some allow

SELECT BM.PARENT, BM.ITEM, IM.DESC
  FROM BM JOIN IM
  WHERE BM.PARENT = 1;

if there's a FK on the ITEM column to the other table.

-- 
Lew

---
 * 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

[toc] | [prev] | [next] | [standalone]

#327

  To: comp.lang.java.databases
On Oct 30, 5:27 am, Lew <no...@lewscanon.com> wrote:
> mdR wrote:
> > SELECT BM.PARENT, BM.ITEM, IM.DESC
> > FROM BM
> > JOIN IM ON IM.ITEM = BM.ITEM
> > WHERE BM.PARENT = 1;
>
> Different RDBMSes vary wrt JOIN syntax.  But RGB's version should have worked,
> too.
>
> Some allow
>
> SELECT BM.PARENT, BM.ITEM, IM.DESC
>   FROM BM JOIN IM
>   WHERE BM.PARENT = 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.)

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS  (http://www.firstsql.com)

---
 * 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

[toc] | [prev] | [next] | [standalone]

#328

  To: comp.lang.java.databases
Lee Fesperman wrote:
> On Oct 30, 5:27 am, Lew <no...@lewscanon.com> wrote:
>> mdR wrote:
>>> SELECT BM.PARENT, BM.ITEM, IM.DESC
>>> FROM BM
>>> JOIN IM ON IM.ITEM = BM.ITEM
>>> WHERE BM.PARENT = 1;
>> Different RDBMSes vary wrt JOIN syntax.  But RGB's version should have worked,
>> too.
>>
>> Some allow
>>
>> SELECT BM.PARENT, BM.ITEM, IM.DESC
>>   FROM BM JOIN IM
>>   WHERE BM.PARENT = 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".

-- 
Lew

---
 * 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

[toc] | [prev] | [next] | [standalone]

#330

  To: comp.lang.java.databases
On Nov 1, 5:17 pm, Lew <no...@lewscanon.com> wrote:
> Lee Fesperman wrote:
> > On Oct 30, 5:27 am, Lew <no...@lewscanon.com> wrote:
> >> mdR wrote:
> >>> SELECT BM.PARENT, BM.ITEM, IM.DESC
> >>> FROM BM
> >>> JOIN IM ON IM.ITEM = BM.ITEM
> >>> WHERE BM.PARENT = 1;
> >> Different RDBMSes vary wrt JOIN syntax.  But RGB's version should have worked,
> >> too.
>
> >> Some allow
>
> >> SELECT BM.PARENT, BM.ITEM, IM.DESC
> >>   FROM BM JOIN IM
> >>   WHERE BM.PARENT = 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".

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.

FK joining is non-standard and begs the question: Which FK for which
table is used?

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS  (http://www.firstsql.com)

---
 * 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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.databases

csiph-web