How to convert data of one table?

Question

PHP

Hello. There are three tables article(id, title, text), tags(id, alias, name), articleTag. The last one is an intermediate table. The third table is needed to filter by tags and add tags to the article. article and tags have an id field, articleTag has an articleID and tagID field. These three tables are related.
This is how I filter them:

"SELECT * FROM `articleTag` AS aT LEFT JOIN `article` AS a ON(aT.articleID = a.id)
                                WHERE aT.tagID = 1"

So I get all the articles where there is a tag with id=1.
Question: the tags table also has an alias(varchar, tag name in English only) field. Is there a way to do a search not by tagID (in the intermediate table) but by alias without changing the connection? Or somehow convert the request above from alias to tag id?

Answer 1

select a.* 
from `article` as a 
         join `articleTag` AS aT ON(aT.articleID = a.id) 
         join `Tags` as t ON (aT.tagID = t.id) 
where t.alias = <условие>

"SELECT * FROM `articleTag` AS aT LEFT JOIN `article` AS a ON(aT.articleID = a.id)
                                WHERE aT.tagID in (select aa.id from `Tags` as aa where aa.alias = <условие>)"

Answer 2

So try

"SELECT * 
FROM `articleTag` AS aT
INNER JOIN `article` AS a ON(aT.articleID = a.id)
INNER JOIN `tags` AS t ON(aT.tagID = t.id)
WHERE AND t.alias = 'find me'"