Thread: 2 tables, joins and same name...
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
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
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
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
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
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;
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
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
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
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
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 >