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

2 tables, joins and same name...

From
Marc André Paquin
Date:
Hello,

Here is 2 tables:

airport
---------
airport_id
name
code
city_id

destination
-----------
destination_id
dest_name
...
airport_dep_id  // using airport.airport_id (departure)
airport_arr_id  // using airport.airport_id  has well (arrival)

I have 2 columns in the second table that uses the same name column in
the first table...

I dont know how to formulate my SQL query... I want to select the
destinations in the destination table with not the ID of each airport
but their names. I can do a join with one but with the second one, I get
no results... And this is confusing!

select dest.dest_name, air.name as airport1, air.name as airport2 from
destination, airport air where dest.airport_dep_id_id=air.airport_id and
dest.airport_arr_id=air.airport_id;

This is not good...

Any help?

Thanks!
--
Marc Andre Paquin


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

From
Richard Poole
Date:
On Thu, Aug 30, 2001 at 04:25:41PM -0400, Marc André Paquin wrote:
> Hello,
> 
> Here is 2 tables:
> 
> airport
> ---------
> airport_id
> name
> code
> city_id
> 
> destination
> -----------
> destination_id
> dest_name
> ...
> airport_dep_id  // using airport.airport_id (departure)
> airport_arr_id  // using airport.airport_id  has well (arrival)
> 
> I have 2 columns in the second table that uses the same name column in
> the first table...
> 
> I dont know how to formulate my SQL query... I want to select the
> destinations in the destination table with not the ID of each airport
> but their names. I can do a join with one but with the second one, I get
> no results... And this is confusing!
> 
> select dest.dest_name, air.name as airport1, air.name as airport2 from
> destination, airport air where dest.airport_dep_id_id=air.airport_id and
> dest.airport_arr_id=air.airport_id;

You have to join against the airport table twice:

SELECT dest.dest_name, air1.name as airport1, air2.name as airport2
FROM desination dest, airport air1, airport 2
WHERE dest.airport_dep_id = air1.airport_id AND dest.airport_arr_id = air2.airport_id;

Richard


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

From
"Josh Berkus"
Date:
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

Attachment

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

From
Thomas Rehlich
Date:
Marc André Paquin wrote:

> Here is 2 tables:
> 
> airport
> ---------
> airport_id
> name
> code
> city_id
> 
> destination
> -----------
> destination_id
> dest_name
> ...
> airport_dep_id  // using airport.airport_id (departure)
> airport_arr_id  // using airport.airport_id  has well (arrival)
> 
> I have 2 columns in the second table that uses the same name column in
> the first table...
> 
> I dont know how to formulate my SQL query... I want to select the
> destinations in the destination table with not the ID of each airport
> but their names. I can do a join with one but with the second one, I
> get no results... And this is confusing!

May be I'm wrong, but I think you mean something like this one

select dest_name     , air1.name as airport1     , air2.name as airport2  from destination  join airport as air1    on
air1.airport_id= destination.airport_dep_id  join airport as air2    on air2.airport_id = destination.airport_arr_id
 


Thomas



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

From
"Josh Berkus"
Date:
Marc,


> I dont know how to formulate my SQL query... I want to select the
> destinations in the destination table with not the ID of each airport
> but their names. I can do a join with one but with the second one, I
> get
> no results... And this is confusing!

Whenever you want to join to the same table twice, you need to use your
table aliases to distinguish between instances of the same table. The
way it's written, the query parser cannot distinguish between the two
instances of the airport table ... so it thinks you're asking for all
flights where the departure and arrival airport are the same.  Which, of
course, is none.

 I'll help with your immediate problem, and then I *highly* suggest you
go out and buy (and read!) Bruce Momjian's book "PostgreSQL:
Introduction and Concepts." (which I believe has been translated if
languages are an issue)

> select dest.dest_name, air.name as airport1, air.name as airport2
> from
> destination, airport air where dest.airport_dep_id_id=air.airport_id
> and
> dest.airport_arr_id=air.airport_id;

SELECT dest.dest_name, depart_air.name as airport1,
       arrive_air.name as airport2
FROM desitination dest JOIN airport depart_air
       ON dest.airport_dep_id=depart_air.airport_id
   JOIN airport arrive_air
       ON dest.airport_arr_id=arrive_air.airport_id

Got it?

-Josh


______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

Attachment

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

From
Stephan Szabo
Date:
On Thu, 30 Aug 2001, Marc [iso-8859-1] Andr� Paquin wrote:

> Hello,
> 
> Here is 2 tables:
> 
> airport
> ---------
> airport_id
> name
> code
> city_id
> 
> destination
> -----------
> destination_id
> dest_name
> ...
> airport_dep_id  // using airport.airport_id (departure)
> airport_arr_id  // using airport.airport_id  has well (arrival)
> 
> I have 2 columns in the second table that uses the same name column in
> the first table...
> 
> I dont know how to formulate my SQL query... I want to select the
> destinations in the destination table with not the ID of each airport
> but their names. I can do a join with one but with the second one, I get
> no results... And this is confusing!
> 
> select dest.dest_name, air.name as airport1, air.name as airport2 from
> destination, airport air where dest.airport_dep_id_id=air.airport_id and
> dest.airport_arr_id=air.airport_id;

You probably want to join airport twice because you want two different
airports. Your query would only get flights from one airport
to itself (look at the where condition, you're saying that the row 
in airport must have an id that is equal to the departure id *and* 
is equal to the arrival id). Probably this:

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;



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

From
"Ross J. Reedstrom"
Date:
On Fri, Aug 31, 2001 at 08:29:21AM -0700, Josh Berkus wrote:
> 
> 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).
> 

Have you seen "Database Design for Mere Mortals" by Michael Hernandez?

And there's another one, that I can only remember as 'the pink book',
I can never remember the title! that struck me as a reasonably good
intro to intermediate level book.

Ross


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

From
"Josh Berkus"
Date:
Ross,

> Have you seen "Database Design for Mere Mortals" by Michael
> Hernandez?

Yeah, that's one I've been loaning out a lot.  However, while it does
cover a lot of good stuff about how to design a database, it never gets
past the most elementary SQL ... really, no further than Bruce gets.
And if I recommend Hernandez together with "SQL for Smarties", well,
that's over 600 pages combined ...

What I'd really love to see, I guess, would be a 200 page "Elements of
SQL" book organized into "lessons" for the beginner.  Maybe with an
additional 75 pages of Q&A examples at the back.

Maybe I should write one.

> And there's another one, that I can only remember as 'the pink book',
> I can never remember the title! that struck me as a reasonably good
> intro to intermediate level book.

Oh, that'll make me friends at Stacy's Bookstore.  "I'm not sure of the
title, and I don't know the author or publisher, but it's about
databases and it's pink."  ;-P

-Josh

______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

Attachment

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

From
"Ross J. Reedstrom"
Date:
On Sat, Sep 01, 2001 at 09:44:19AM -0700, Josh Berkus wrote:
> Ross,
> 
> > Have you seen "Database Design for Mere Mortals" by Michael
> > Hernandez?
> 
> Yeah, that's one I've been loaning out a lot.  However, while it does
> cover a lot of good stuff about how to design a database, it never gets
> past the most elementary SQL ... really, no further than Bruce gets.
> And if I recommend Hernandez together with "SQL for Smarties", well,
> that's over 600 pages combined ...
> 
> What I'd really love to see, I guess, would be a 200 page "Elements of
> SQL" book organized into "lessons" for the beginner.  Maybe with an
> additional 75 pages of Q&A examples at the back.
> 
> Maybe I should write one.
> 
> > And there's another one, that I can only remember as 'the pink book',
> > I can never remember the title! that struck me as a reasonably good
> > intro to intermediate level book.
> 
> Oh, that'll make me friends at Stacy's Bookstore.  "I'm not sure of the
> title, and I don't know the author or publisher, but it's about
> databases and it's pink."  ;-P

I can see it "No, database, not dating, really!"

Poking around Amazon, I find:

http://www.amazon.com/exec/obidos/ASIN/0201447878/ref=pd_sim_books/102-0290590-5673700

The Practical Sql Handbook : Using Structured Query Language

which has the shocking pink cover. The've got a "Sequel", with supposedly
more advanced topics which is green.

And Hernandez has an "SQL Queries for Mere Mortals"

I haven't seen _any_ of these books for over a year, and know a lot more
SQL than I did then, so take any recommendations with a grain of salt.


Ross


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

From
"Josh Berkus"
Date:
Ross,

> I haven't seen _any_ of these books for over a year, and know a lot
> more
> SQL than I did then, so take any recommendations with a grain of
> salt.

Hmmm... both of these books get good reviews.

Is there anywhere (say, techdocs) where we could add a book list?  I'll
ask ...

-Josh


______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

Attachment

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

From
"Jeff Eckermann"
Date:
I learned SQL from Sam's "Teach Yourself SQL in 21 Days", and am happy to
recommend it.  Which book is "best" is very subjective and situation
dependent; all I can say is that this one did the job for me.
Pros: clearly written; knowledgable authors; good coverage
Cons: all due to space limitations.  Many advanced features are touched on,
without any really useful explanation of how to use them or even why they
exist.  Can be a good starting point though (The pl/pgsql docs made no sense
at all to me, until I had read the Oracle pl/sql coverage in this book).

----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Ross J. Reedstrom" <reedstrm@rice.edu>; "Josh Berkus"
<josh@agliodbs.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Saturday, September 01, 2001 11:44 AM
Subject: Re: 2 tables, joins and same name...


> Ross,
>
> > Have you seen "Database Design for Mere Mortals" by Michael
> > Hernandez?
>
> Yeah, that's one I've been loaning out a lot.  However, while it does
> cover a lot of good stuff about how to design a database, it never gets
> past the most elementary SQL ... really, no further than Bruce gets.
> And if I recommend Hernandez together with "SQL for Smarties", well,
> that's over 600 pages combined ...
>
> What I'd really love to see, I guess, would be a 200 page "Elements of
> SQL" book organized into "lessons" for the beginner.  Maybe with an
> additional 75 pages of Q&A examples at the back.
>
> Maybe I should write one.
>
> > And there's another one, that I can only remember as 'the pink book',
> > I can never remember the title! that struck me as a reasonably good
> > intro to intermediate level book.
>
> Oh, that'll make me friends at Stacy's Bookstore.  "I'm not sure of the
> title, and I don't know the author or publisher, but it's about
> databases and it's pink."  ;-P
>
> -Josh
>
> ______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
>


----------------------------------------------------------------------------
----


>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>