CODEWITHSUNDEEP
mysqlsqlrelational-algebra
rustycode
rustycode

Reputation: 83

Proving relational calculus with SQL

For an assignment I am tasked with writing a relational calculus query for this statement,

Which author co-authored at least 1 paper with every author (without aggregate functions)

Given this schema, 

Authors( auId, name)
Authoring( articleId, authorId )

I wanted to prove my query using sql first, but I have noticed that I am getting only the authors who co-authored every article, when I need only the authors who co-authored at least on article with every other author. How might I change my query to achieve the desired result? Thanks.

Here is the query I came up with,

select distinct authorid
from authoring as au1
where not exists(
    select *
    from authoring as ar1
    where not exists(
        select *
        from  authoring as a2
        where a2.articleid = ar1.articleid
          and a2.authorid  = au1.authorid
));

The result I am getting is
authorId
--------
1

The result should be 1,2 because authorID 2 shares a article with both authors   1 & 3

authorId
--------
1
2

Authors Table

auid | name
-----------
1    | Michael
2    | Jazmine
3    | Amanda

Authoring Table
articleId | authorId
--------------------
1         | 1
2         | 1
3         | 1
1         | 2
3         | 2
1         | 1

I have posted an answer to this question below.

Upvotes: 0

Views: 343

Answers (1)

rustycode
rustycode

Reputation: 83

The SQL Query to find all authors who co-authored at least one article with every other author.

select *
from `authors` as a1
where not exists(
    select *
    from `authors` as a2
    where a2.auid <> a1.auid
    and not exists(
        select *
        from authoring as ar1
        inner join authoring as ar2
            on ar1.articleid = ar2.articleid
        where ar1.authorid    = a1.auid
        and   ar2.authorid    = a2.auid
));

Upvotes: 1

Related Questions