Обсуждение: Quick join query

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

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)?