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 :

The writers have papers they write…

papers :

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:

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 );

Output:

Query 2 (using join):

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

Output:

db<>fiddle here