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

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

joining

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

Contents

  joining Roedy Green <see_website@mindprod.com.invalid> - 2014-08-08 05:04 -0700
    Re: joining Lothar Kimmeringer <news200709@kimmeringer.de> - 2014-08-08 15:54 +0200
      Re: joining Chris Riesbeck <Chris.Riesbeck@gmail.com> - 2014-08-08 11:55 -0500
        Re: joining Roedy Green <see_website@mindprod.com.invalid> - 2014-08-08 19:19 -0700
          Re: joining Silvio <silvio@internet.com> - 2014-08-09 07:06 +0200
            Re: joining Roedy Green <see_website@mindprod.com.invalid> - 2014-08-09 02:45 -0700
              Re: joining Silvio <silvio@internet.com> - 2014-08-09 12:16 +0200
                Re: joining Chris Riesbeck <Chris.Riesbeck@gmail.com> - 2014-08-11 12:23 -0500

#680 — joining

I have a very simple application.
I have records for quotations and records for authors.
Each quotation has as most one author, but each author can have many
quotations.

I look up the quotation, then I want to find the author record.

What I do now is have a unique integer id in the author record and I
look it up separately.

I suspect it might be faster/better to get SQL to join the records for
me and present me with one unified record. I don't want collections,
just a single author-quotation record.

Is that a better approach and if so, what does a query look like given
each record has a unique integer key?
-- 
Roedy Green Canadian Mind Products http://mindprod.com
The art of strongly-typed language design is largely arranging 
that errors are automatically detected as soon as possible
in the compose, compile, run cycle.

[toc] | [next] | [standalone]

#681

Roedy Green wrote:

> I suspect it might be faster/better to get SQL to join the records for
> me and present me with one unified record.

If you mean resultset when speaking of record, it is.

> Is that a better approach and if so, what does a query look like given
> each record has a unique integer key?

Not sure, where the java-related question is, but the statement
would look like this:

Table authors:

id int
name varchar
...

Table quotes:
id int
id_author int
quote text
...


select a.name, b.quote from authors a, quotes b
where a.id = b.id_author


Cheers, Lothar
-- 
Lothar Kimmeringer                E-Mail: spamfang@kimmeringer.de
               PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
                 questions!

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

#682

On 8/8/2014 8:54 AM, Lothar Kimmeringer wrote:
> Roedy Green wrote:
>
>> I suspect it might be faster/better to get SQL to join the records for
>> me and present me with one unified record.
>
> If you mean resultset when speaking of record, it is.
>
>> Is that a better approach and if so, what does a query look like given
>> each record has a unique integer key?
>
> Not sure, where the java-related question is, but the statement
> would look like this:
>
>
> select a.name, b.quote from authors a, quotes b
> where a.id = b.id_author

Or,

   select a.name, b.quote from authors a
   join quotes b on a.id = b.id_author

I prefer using JOIN for joins and WHERE clauses for other constraints. 
Also makes it easier to modify the type of JOIN if that becomes an issue.

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

#683

On Fri, 08 Aug 2014 11:55:25 -0500, Chris Riesbeck
<Chris.Riesbeck@gmail.com> wrote, quoted or indirectly quoted someone
who said :

>   select a.name, b.quote from authors a
>   join quotes b on a.id = b.id_author

a and b are just placeholders, right?

That gets you the set of all quotes, matched with the corresponding
author.

To get just one quote:

select authors.name, quotes.quote from authors join quotes on
authors.id = quote.id_author where quotes.id = nnnn;


That looks like it constructs a set of all the author's quotes, then
selects just one of them. Does it? Or is that just what it
conceptually does?



-- 
Roedy Green Canadian Mind Products http://mindprod.com
The art of strongly-typed language design is largely arranging 
that errors are automatically detected as soon as possible
in the compose, compile, run cycle.

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

#684

On 08/09/2014 04:19 AM, Roedy Green wrote:
> On Fri, 08 Aug 2014 11:55:25 -0500, Chris Riesbeck
> <Chris.Riesbeck@gmail.com> wrote, quoted or indirectly quoted someone
> who said :
>
>>    select a.name, b.quote from authors a
>>    join quotes b on a.id = b.id_author
>
> a and b are just placeholders, right?
>
> That gets you the set of all quotes, matched with the corresponding
> author.
>
> To get just one quote:
>
> select authors.name, quotes.quote from authors join quotes on
> authors.id = quote.id_author where quotes.id = nnnn;
>
>
> That looks like it constructs a set of all the author's quotes, then
> selects just one of them. Does it? Or is that just what it
> conceptually does?
>
>
>

That is what it conceptually does. In SQL joins create sets (tables) 
that combine tuples (records) from existing ones. Using selection (where 
conditions) and projection (columns you select) operators reduces such 
sets to the data you actually require. The RDBMS is responsible of 
collecting that information in the most efficient way. And they are 
extremely good at doing that. A lot better than what any program can 
achieve by using it as a contemporary ISAM implementation.

The short answer: put as much data access logic as possible in your SQL 
and let the database worry about it.

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

#685

On Sat, 09 Aug 2014 07:06:31 +0200, Silvio <silvio@internet.com>
wrote, quoted or indirectly quoted someone who said :

>That is what it conceptually does. In SQL joins create sets (tables) 
>that combine tuples (records) from existing ones. Using selection (where 
>conditions) and projection (columns you select) operators reduces such 
>sets to the data you actually require. The RDBMS is responsible of 
>collecting that information in the most efficient way. And they are 
>extremely good at doing that. A lot better than what any program can 
>achieve by using it as a contemporary ISAM implementation.

The way I handle it now, I create a quote record, then look up a
author record by name, if I find one, I put its uniqueid number in the
quote record.  If it does not exist, I create one, fill in the fields.
and put its unique id number in the quote record.

Can that be handled as if there were one unified empty joined record
and I fill in all the fields.  It then hooks everything up, creating
as new author record as needed? or is join just for lookup?

-- 
Roedy Green Canadian Mind Products http://mindprod.com
The art of strongly-typed language design is largely arranging 
that errors are automatically detected as soon as possible
in the compose, compile, run cycle.

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

#686

On 08/09/2014 11:45 AM, Roedy Green wrote:
> On Sat, 09 Aug 2014 07:06:31 +0200, Silvio <silvio@internet.com>
> wrote, quoted or indirectly quoted someone who said :
>
>> That is what it conceptually does. In SQL joins create sets (tables)
>> that combine tuples (records) from existing ones. Using selection (where
>> conditions) and projection (columns you select) operators reduces such
>> sets to the data you actually require. The RDBMS is responsible of
>> collecting that information in the most efficient way. And they are
>> extremely good at doing that. A lot better than what any program can
>> achieve by using it as a contemporary ISAM implementation.
>
> The way I handle it now, I create a quote record, then look up a
> author record by name, if I find one, I put its uniqueid number in the
> quote record.  If it does not exist, I create one, fill in the fields.
> and put its unique id number in the quote record.
>
> Can that be handled as if there were one unified empty joined record
> and I fill in all the fields.  It then hooks everything up, creating
> as new author record as needed? or is join just for lookup?
>

No, you can not do that in one go. In some cases you can update a join 
but I do not know of any RDBMS that supports inserting into joins.

There are two approaches here: you either check on the presence of the 
author name by selecting its id by name or you insert (name,id) and let 
the insert fail (return 0 records updated) if the name is already 
present by adding a unique index on the author name (which is probably a 
good idea anyway).

The first way you will then have to insert the author if the name is not 
present.

Either way you will have the id of the author to subsequently insert the 
quote.

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

#687

On 8/9/2014 5:16 AM, Silvio wrote:
> On 08/09/2014 11:45 AM, Roedy Green wrote:
>> On Sat, 09 Aug 2014 07:06:31 +0200, Silvio <silvio@internet.com>
>> wrote, quoted or indirectly quoted someone who said :
>>
>> The way I handle it now, I create a quote record, then look up a
>> author record by name, if I find one, I put its uniqueid number in the
>> quote record.  If it does not exist, I create one, fill in the fields.
>> and put its unique id number in the quote record.
>>
>> Can that be handled as if there were one unified empty joined record
>> and I fill in all the fields.  It then hooks everything up, creating
>> as new author record as needed? or is join just for lookup?
>>
>
> No, you can not do that in one go. In some cases you can update a join
> but I do not know of any RDBMS that supports inserting into joins.
>
> There are two approaches here: you either check on the presence of the
> author name by selecting its id by name or you insert (name,id) and let
> the insert fail (return 0 records updated) if the name is already
> present by adding a unique index on the author name (which is probably a
> good idea anyway).

For various reasons, you might be better off first retrieving on name, 
with some intelligent fuzzy matching, anyway, to avoid entering 
duplicate and erroneous author names. If you really wanted to get fancy, 
have an author ID table that you join with an id-name table, to handle 
authors with more than one commonly used name, e.g., Mark Twain / Samuel 
Clemens.

[toc] | [prev] | [standalone]

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

csiph-web