Skip site navigation (1) Skip section navigation (2)

Re: Quick join query

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: SG Edwards <s0460205(at)sms(dot)ed(dot)ac(dot)uk>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Quick join query
Date: 2005-04-22 10:30:53
Message-ID: 005af6ad4f1e8c800a188fc91dc94d10@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
On Apr 22, 2005, at 4:53 AM, SG Edwards wrote:

> Hi,
>
> I have four tables which I wish to join into a view using the 
> following code:
>
> CREATE VIEW web_search AS
> SELECT * FROM basic_search
> LEFT JOIN comment USING (bioentry_id)
> LEFT JOIN bioentry_reference USING (bioentry_id)
> LEFT JOIN taxon_name USING (taxon_id);
>
> I have two questions regarding this creation:
>
> 1. If I only want to join one column from the table "comment" rather 
> than the
> whole table, is there a way to do this with a create view statement? 
> (comment
> table has 4 columns Comment_id, Bioentry_id, Comment_text, Rank but I 
> just want
> comment_text to be joined to basic search).
>
> 2. The last join throws an error because a column in taxon_name has 
> the same
> name as one in basic search ("name"). Is there a way to change a 
> column name
> within the join statement?
>

I would rewrite this something like:

CREATE VIEW web_search AS
SELECT
	b."Comment_text",a.*,c.*,d.*
FROM
	basic_search a
		left join
	comment b            USING (bioentry_id)
		left join
	bioentry_reference c USING (bioentry_id)
		left join
	taxon_name d         USING (taxon_ID);

Note the small differences from your query (and note that I didn't test 
this directly).  First, each table name is followed by a letter that 
becomes the alias for that table.  Second, by using c.*, we can say 
"choose all columns (*) from comment (aliased as 'b')".  The error due 
to two tables having the same "name" column is not due directly to the 
join, but the fact that your query is putting together all the columns 
from all the tables.  As I mentioned, you can refer to a specific 
column from a specific table using syntax like:

d.name

which is a different column than

a.name

Hope this helps a bit.

Sean

P.S. Which schema is this?  BioSQL or chado (or something else)?


In response to

pgsql-novice by date

Next:From: sandyDate: 2005-04-22 11:10:29
Subject: Base type ...and array of base type
Previous:From: SG EdwardsDate: 2005-04-22 08:53:10
Subject: Quick join query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group