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

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

CodePudding user response：

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:

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

Output:

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

db<>fiddle here

CodePudding user response：

You can join both tables together using the writers table primary key and papers table foreign key, then specify the boss_id in a where clause.

SElECT p.* 
FROM papers p
INNER JOIN writers w ON p.writer_id = w.id
WHERE w.boss_id = 4