Re: 2 tables, joins and same name...

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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco