sql triple join: ambigious attribute name on a count

Question

So I want to count a number of books, but the books are stored in 2 different tables with the same attribute name. I want to get a result that looks like:

  name1 [total number of books of 1]
  name2 [total number of books of 2]

I tried this triple join;

SELECT DISTINCT name, count(book)
FROM writes w
LEFT JOIN person p on p.id = w.author
LEFT JOIN book b on b.title = w.book
LEFT JOIN controls l on l.controller=p.id
GROUP BY name
ORDER BY name DESC

but since book exists as an attribute in writes and in controls, it cant execute the query. It can only do it if I leave out one of joins so it can identify book.

How can I tell the sql engine to count the number of both book attributes together for each person?

name isnt the problem, the count on book is – pltm_dev Dec 15 '11 at 21:13 — pltm_dev, Dec 15 '11 at 21:13

score 2 · Accepted Answer · answered Dec 15 '11 at 21:24

As a result of database design that you interested in, you should issue 2 different sql and then merge them to handle single output.

A)

SELECT DISTINCT w.name as 'Name', count(w.book) as 'Cnt'
FROM writes w
LEFT JOIN person p on p.id = w.author
LEFT JOIN book b on b.title = w.book

B)

SELECT DISTINCT l.name as 'Name', count(l.book) as 'Cnt'
FROM controls l
LEFT JOIN person p on p.id = l.controller
LEFT JOIN book b on b.title = l.book

For your purpose, you can get UNION of A and B.

or you can use them as data source on a third SQL

select A.Name, sum(A.Cnt+B.Cnt) 
from  A,  B 
where A.Name = B.Name
group by A.Name
order by A.Name

score 1 · Answer 2 · answered Dec 15 '11 at 21:14

1

WITH T AS 
( 
  SELECT DISTINCT 'WRITES' FROMTABLE, w.name, w.count(book) 
    FROM writes w
    LEFT JOIN person p on p.id = w.author
    LEFT JOIN book b on b.title = w.book
  GROUP BY name
  UNION ALL 
  SELECT DISTINCT 'CONTROLLS' FROMTABLE, c.name, count(c.book) 
    FROM controlls c
    LEFT JOIN person p on p.id = c.author
    LEFT JOIN book b on b.title = c.book
  GROUP BY name
)
SELECT * FROM T ORDER BY NAME

Should work.

HTH

answered Dec 15 '11 at 21:14

Zsolt Botykai

50,406
14
85
110

I'm sorry for not checking every question of their OPs for tags... Anyway: http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql – Zsolt Botykai Dec 15 '11 at 21:23

score 0 · Answer 3 · answered Dec 15 '11 at 21:35

This will work on a per distinct author's ID to how many books they've written. The pre-aggregation will return one record per author with how many books by that author. THEN, join to the person table to get the name. The reason I am leaving it by ID and Name of the author is... what if you have two authors "John Smith", but they have respective IDs of 123 and 389. You wouldn't want these rolled-up to the same person (or do you).

select
      P.ID,
      P.Name,
      PreAgg.BooksPerAuthor
   from
      ( select
              w.author,
              count(*) BooksPerAuthor
           from
              writes w
           group by
              w.author ) PreAgg
      JOIN Person P
         on PreAgg.Author = P.id
   order by 
      P.Name

sql triple join: ambigious attribute name on a count

3 Answers3