mysql problem with union/join used in conjunction

Question

I have these two tables:

Table Author:

ID (INTEGER),
author_name (VARCHAR),
first_name (VARCHAR),
last_name (VARCHAR),
preferred_name (VARCHAR)

Table CoAuthored:

ID (INTEGER)
author1ID (INTEGER),
author2ID (INTEGER),
paper_ID (INTEGER) // (ID of the co-authored paper referenced by this link)

As you can see, both tables have a column 'ID', but they are unrelated.

However, when I use this query:

select author_name, count(*) as NumPapers from
(select * from Author as a join CoAuthored as c on a.ID = c.author1ID
union distinct
select * from Author as b join CoAuthored as d on b.ID = d.author2ID) as t1
group by author_name
order by NumPapers;

mySQL gves me an error saying: ERROR 1060 (42S21): Duplicate column name 'ID'

Why does this happen and how do I avoid it?

Answer 1

Rather than select * ... use select author_name ... in the two subqueries being unioned. The problem stems from both Author and CoAuthored having ID columns. SELECT * brings through both of these columns and MySQL doesn't like the idea of UNIONing these to produce a resultset with two same-named columns.

Answer 2

Try this:

select author_name, count(*) as NumPapers from 
(
    select a.id, a.author_name from Author as a 
    join CoAuthored as c on a.ID = c.author1ID
    union distinct
    select b.id, b.author_name from Author as b
    join CoAuthored as d on b.ID = d.author2ID
) as t1
group by author_name
order by NumPapers;

Since you do not need the ID column from CoAuthored, you can just not select it in the inner query. This should remove your duplicate column error since it is only selecting 1 of the ID columns.

Answer 3

what about:

SELECT author_name, COUNT(*) AS NumPapers
  FROM Author AS a
  JOIN CoAuthored AS c ON a.ID = c.author1ID OR a.ID = c.author2ID
 GROUP BY author_name
 ORDER BY NumPapers;