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

Quick join query

From: SG Edwards <s0460205(at)sms(dot)ed(dot)ac(dot)uk>
To: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Quick join query
Date: 2005-04-22 08:53:10
Message-ID: 1114159990.4268bb7626a76@sms.ed.ac.uk (view raw or flat)
Thread:
Lists: pgsql-novice
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?

Many thanks for your help,

Stephen

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2005-04-22 10:30:53
Subject: Re: Quick join query
Previous:From: Pradeepkumar, Pyatalo (IE10)Date: 2005-04-22 08:28:57
Subject: Re: Disk full problem

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