How to use "join" in postgres

Question

I have two table,

authorCollection contains columns: author, key.
book contains columns: key, title, type,....etc.

There are many types for book, I hope to select the type is not 'UNKNOWN' I hope to join these two table as following:

SELECT A.key, A.author,
       I.key, I."Type"
FROM   authorCollection AS A
JOIN   book AS I
ON  A.key = I.key AND I."TYPE" <> 'UNKNOWN';

But the result I get is: there are two column called "key", and author and type. I hope there is just one "key" column for the result, because they are same.

How can I fix this? I try "natural join", but not solved. Thanks.

fixed above

based on this, I need to group by type and author:

SELECT A.key, A.author, I."Type"
FROM authorCollection A JOIN
     book I
     ON  A.key = I.key AND I."TYPE" <> 'UNKNOWN'
GROUP BY A.author, I."Type";

And then I hope to find the author's name who only appear two times in different types book. For example:

author    type
tom        1
tom        2
tom        3
alex       3
alex       3
tony       1
tony       1

The result is tony and alex, their book appears in two different types. tom appears in three types, so it is not a result. How can I write query statement to realize it? THANKS.

Gordon Linoff · Accepted Answer · 2016-01-30 19:02:26Z

0

Just put the columns you want in the select. You have two that are called KEY:

SELECT A.key, A.author, I."Type"
FROM authorCollection A JOIN
     book I
     ON  A.key = I.key AND I."TYPE" <> 'UNKNOWN';

answered Jan 30, 2016 at 19:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bog Over a year ago

It works! I still have another question, change in original question. Can you help me? Thanks

MortenSickel Over a year ago

Bog, it is better to write that as a new question.

Collectives™ on Stack Overflow

How to use "join" in postgres

How can I fix this? I try "natural join", but not solved. Thanks.

fixed above

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

How can I fix this? I try "natural join", but not solved. Thanks.

fixed above

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related