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