How will this query be rewritten using JOIN?


How will this query be rewritten using JOIN?

There are 4 tables at least, like these:

1. PostsTable
pid, stat, title, etc etc

2. tagsTable
id, pid, tag

3. profilesTable
profileID, username, etc etc

4. ratingsTable
id, pid, profileID, etc etc
SEMrush

Currently am using:

select something something
from postsTable as p, profilesTable as pr, ratingsTable as r, tagsTable as t
where
p.stat=’y’ and
t.pid = p.pid and
r.pid = p.pid and
pr.profileID = r.profileID

“something something” are columns from profilesTable and from postsTable and from tagsTable.

I was hoping to do it using join statement, something like this:

select something something
from postsTable as p
left join tagsTable as t
on t.pid = p.pid
left join ratingsTable as r
on r.pid = p.pid
( this is where I am lost )
No idea how to connect ratingsTable to profilesTable here…
At this point I have to select username in profilesTable using profileID given in ratingsTable

I want to avoid creating a new table connecting postsTable to profilesTable for just this purpose…
profileID is already there in ratingsTable, I am already reading ratingsTable
So I do not want to create new connecting table for profileID and pid alone and read that also…

Thanks