pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Web Manager <web@inter-resa.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: 2 tables, joins and same name...
Date: Fri, 31 Aug 2001 08:29:21 -0700
Message-ID: <web-111895@davinci.ethosmedia.com> (raw)
In-Reply-To: <3B8FA548.6820600B@inter-resa.com>

Marc,

> Yes, but I used this instead:
> select dest.dest_name, air1.name as airport1, air2.name as airport2
> from
> destination, airport air1, airport air2 where dest.airport_dep_id=
> air1.airport_id and dest.airport_arr_id=air2.airport_id;
> 
> This is very similar to your query... I think the join is implicit
> instead of explicit like yours.

You are correct.  That query should work fine.

It will be useful for you to know both join syntaxes.  There are times
when an explicit join is required (LEFT OUTER JOIN, for example) and
usually it's clearer for others to read in your code.

> PS  for the book, yes It could be useful but when you dont know what
> to
> look for (I have 2 SQL book reference), I just found out that this is
> a
> self join.

That's why we have the list.  Personally, I'm still looking for a
comprehensive introductory SQL book to recommend.  The ones I know are
either too simple and not that accurate (SQL for Dummies), proprietary
(MS SQL Server in 24 hours), too short (PostgreSQL Introduction and
Concepts*), or too advanced for the newbie (SQL for Smarties).

I reccomended the PostgreSQL book for a variety of reasons, not the
least of which is the number of languages it's been translated into.  I
know from personal experience that it is hard enough finding the right
reference in your native language.  Plus Bruce provides quite a number
of good examples.  Plus Bruce is on this list.  Hi, Bruce!

And, technically, what you did is not a "self join".  This would be a
Self Join:

SELECT node1.id, node2.id
FROM nodes node1 JOIN nodes node2 
    ON node1.id = node2.parent_id;

... where you are joining a table to itself.  Makes sense, yes?


What you did was join the same table, twice, to a third table.  I don't
believe that this structure has a particular name.  It's very common.

-Josh Berkus

*= Bruce, what I mean by "too short" is that you only have about 80
pages of introduction to SQL, which makes it a good first intro but does
not bridge the gap between "What's a query?" and Fabian Pascal.  Which
makes it good but not comprehensive.

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







view thread (11+ messages)  latest in thread

Message-ID: <web-111895@davinci.ethosmedia.com>
Permalink:  ../web-111895@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-111895@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, web@inter-resa.com
  Subject: Re: 2 tables, joins and same name...
  In-Reply-To: <web-111895@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox