Re: Quick join query

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