0
select name 
from person, author, article
where name != "John Doe" AND
      person.pid = author.pid AND
      author.aid = article.aid AND
        title = select title
                from author, person, article
                where person.name = "John Doe" AND
                      author.pid = person.pid AND
                      article.aid = author.aid

The above are the nested queries I wrote in sqlite for a relational algebra that outputs the names of the people who coauthored an article with John Doe.

Here's the relational schema:
Article(aid, title, year, confid, numpages) Author(aid, pid) Person(pid, name, affiliation).
My question how can I simplify the nested queries?

Han
  • 11
  • 5
  • This is not clear. What are you asking for? SQL or RA (relational algebra)? What do you mean, "the RA" of this query? What is "what I have now"? What does your title mean? What is your SQL--MySQL or SQLite? Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. PS If you're stuck writing a RA query: RA is code, so give as much of a [mre] as you can & also google 'run relational algebra online'. If you are trying to write SQL: [Replace comparison to scalar subquery by inner join or left/right join](https://stackoverflow.com/q/28202970/3404097) – philipxy Oct 17 '20 at 02:23

1 Answers1

1

I don't see how your question relates to regular expressions at all. If you want the names of the persons that co-authored with John Doe though, I would recommend joins:

select distinct pe2.name
from person pe1
inner join author  au1 on au1.pid = pe1.pid
inner join author  au2 on au2.aid = au1.aid and au2.pid <> au1.pid
inner join person  pe2 on pe2.pid = au2.pid
where pe1.name = 'John Doe'

The query starts from person John Doe, and brings the corresponding rows in author; then, it searches for all co-authors, and finally brings their names.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Oh sorry I mean relational algebra. – Han Oct 17 '20 at 01:00
  • The RA would correspond fairly closely to GMB's SQL. What have you tried so far? Beware there are many varieties of RA, that differ as to what operators are available. Your q needs to spell out with variety you're using. – AntC Oct 18 '20 at 20:28