3

I am a Junior Developer. I have been asked to create a subquery to solve this problem. If the question is not clear then please let me know so I can provide more details.

I have two tables,Article and Author. I have been tasked with doing the following:

Select distinct Author values that don't exist in the Author table. The result should not contain any Authors that are NULL or Empty String. The purpose is to find any Author names that ARE in the Article table but NOT in the Author table.

I have Author column in both Tables. Can this problem be solved by joining these two tables?

I have tried

select distinct name 
from author 
where name NOT IN ( select author 
                    from article 
                    where author IS NOT NULL)

It doesn't give me the result that I want :(

  • 1
    If you want in Article and not in Auther then that query is backwards – paparazzo May 4 '16 at 13:17
  • I suspect that this question can be confusing for beginners. You are using the name "Author" for both a table name and a field, right? And that is probably why I am confused by "select distinct Author values that don't exist in the Author table". I think "find any Author names that ARE in the Article table but NOT in the Auther table" explains it. – user34660 Dec 26 '18 at 2:51
  • Also you say Author column in both Tables but the name is name in the author table, correct? – user34660 Dec 26 '18 at 3:08
-1

I assume that you have the following tables:

CREATE TABLE Author(AutorID INT, Name VARCHAR(50));  
CREATE TABLE Article(ArticleID INT, Article VARCHAR(MAX),Author VARCHAR(50));

Now to achieve your result just run the following query:

SELECT DISTINCT Article.Author WHERE Article.Author NOT IN 
(SELECT Author.Name FROM Author) AND Article.Author IS NOT NULL
AND Article.Author != ''; 
5
select author from article where author IS NOT NULL and author <> ''
except 
select name   from author

since any comparison the null is false I think below works

select author from article where author <> ''
except 
select name   from author
  • This is the most elegant reply - +1. If there are article.authors which are blank strings in article.author - you need to add AND author != ''. – Vérace May 4 '16 at 15:19
  • This might be confusing for beginners. In select author, author is a field, right? In from author, author is a table, correct? Well at least this comment might help someone in the future. – user34660 Dec 26 '18 at 3:03
0

You need only one column in the final output from Article table. So need to join Article and Author table. This can be done using subquery. Please Try this:

select distinct name 
from article 
where name not in (select name from Author) 
and name is not null 
and name != ''

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.