SQL subquery or JOIN on two tables, match to once-removed value

Consider these tables:

Based on id

  • Bill is John’s boss
  • Hank is Andy’s and Alex’ boss

writers :

id name boss_id
1 John 2
2 Bill 2
3 Andy 4
4 Hank 4
5 Alex 4

The writers have papers they write…

papers :

id title writer_id
1 Boston 1
2 Chicago 4
3 Cisco 3
4 Seattle 2
5 North 5

I need an SQL query that will return the papers.* for every paper written by writers working for Hank (id = 4 ==> boss_id = 4). That happens to include Hank himself because he is registered as his own boss, but that’s not so important.

Desired output:

id title writer_id
2 Chicago 4
3 Cisco 3
5 North 5

What I have, that has no hope of working:

SELECT * FROM papers p WHERE ( writers w AS p.writer_id JOIN w.boss_id = 4 );

>Solution :

You are on right track to solve the problem. Here I am sharing two approaches: one is by using sub query and the other is by joining both tables.

Schema and insert statements:

 create table writers( id int, name varchar(200), boss_id int);
  insert into writers values( 1,'John',2);
  insert into writers values( 2,'Bill',2);
  insert into writers values( 3,'Andy',4);
  insert into writers values( 4,'Hank',4);
  insert into writers values( 5,'Alex',4);
  create table papers ( id int, title varchar(200),writer_id int);

  insert into papers values( 1,'Boston',1);
  insert into papers values( 2,'Chicago',4);
  insert into papers values( 3,'Cisco' ,3);
  insert into papers values( 4,'Seattle',2);
  insert into papers values( 5,'North' ,5);

Query 1( using sub query):

 SELECT * FROM papers p WHERE writer_id in ( select id from writers where boss_id = 4 );


id title writer_id
2 Chicago 4
3 Cisco 3
5 North 5

Query 2 (using join):

  select p.* from papers p 
                  inner join writers w on p.writer_id=w.id
  where w.boss_id=4


id title writer_id
3 Cisco 3
2 Chicago 4
5 North 5

db<>fiddle here

Leave a Reply