запрос из 3 таблиц

[carrew]

Не могу реализовать следующий запрос: Кто из авторов никогда не писал статьи в соавсторстве.

Структура 3 таблиц.
Вот моя реализация нос ошибкой "SUM_ART": invalid identifier

SELECT Author.id_author, Author.name, SUM(i.id_author) sum_art, Article.title
FROM Authors Author
INNER JOIN id i ON i.id_author = Author.id_author
INNER JOIN Article Article ON i.id_article = Article.id_article
WHERE sum_art = 1
GROUP BY sum_art;

Работаю в Oracle.

[Аватар]

SELECT R.id_author,Authors.name
  FROM (SELECT id_author,COUNT(*) AS CountAuthor
          FROM id
          GROUP BY id_author) R
    LEFT JOIN Authors ON Authors.id_author=R.id_author
  WHERE R.CountAuthor=1

SELECT Authors.id_author,Authors.name
  FROM Authors
  WHERE (SELECT COUNT(*) FROM id WHERE id.id_author=Authors.id_author)=1

[carrew]

Все равно не верно выводит запрос результат.

SELECT R.id_author,Authors.name
  FROM (SELECT id_author,COUNT(*) AS CountAuthor
          FROM id
          GROUP BY id_author) R
    LEFT JOIN Authors ON Authors.id_author=R.id_author
  WHERE R.CountAuthor=1

Вот структура заполненых данных всех 3 таблиц. В результате должен вывестись автор Andrey Ivanov, Edward Jons, Endi Gutmans.

[evg_m]

--число автров статьи
select id_article, count(*) as countautor 
from id 
group by id_article

--авторы писавшие в сооавтростве (имеющие статьи с числом авторов >1)
select id_autor  
from id
inner join ( select id_article, count(*) as countautor 
             from id group by id_article
             group by id_article 
         ) as a2 on a2.id_article =id.id_article
where a2.countauror>1 

--прочие авторы
select * 
from authors
where id_autor not in ( select id_autor 
                        from id
                        inner join ( select id_article, count(*) as countautor 
                                     from id group by id_article
                                     group by id_article 
                                   ) as a2 on a2.id_article =id.id_article
                        where a2.countauror>1  )

select id_article, count(*) as countautor 
from id 
group by id_article

select id_autor, max(countautor) as multi
from id
inner join ( select id_article, count(*) as countautor 
             from id group by id_article
             group by id_article 
         ) as a2 on a2.id_article =id.id_article
group by id_autor

select * 
from authors
left join ( select id_autor, max(countautor) as multi
            from id
            inner join ( select id_article, count(*) as countautor 
                         from id group by id_article
                         group by id_article 
                       ) as a2 on a2.id_article =id.id_article
            group by id_autor 
          ) as m2 on m2.id_autor.authors.is_autor
where m2.multi<=1

[carrew]

смог только

select id_article, count(*) as countautor
from id
group by id_article

запустить. Дальше ошибки, сделал исправления название столбоцов все равно не работает.

[Аватар]

А так

SELECT Authors.id_author,Authors.name
  FROM Authors
  WHERE NOT EXISTS(
    SELECT *
      FROM id,(SELECT id_article,COUNT(*) AS CountAuthor FROM id GROUP BY id_article) AS R
      WHERE Authors.id_author=id.id_author AND id.id_article=R.id_article AND R.CountAuthor>1)

[carrew]

Что означает ошибка missing right parenthesis ?

[Аватар]

Орёт, что нет правой скобки. А вообще запрос для T-SQL. Как там оракл это поймет не скажу

[Vapaamies]

По виду оба запроса из сообщения #2 для Oracle валидны.