mysql - 在 mysql select 语句中重用内部查询

Question

mysql> select a.catname as parent , b.catname as child from cmscat a , cmscat b where  
a.catrefid = (select ccmcatrefid1 as parentId from cmsccm where ccmrefid = 141) and 
b.catrefid= (select ccmcatrefid2 as childId from cmsccm where ccmrefid = 141);

+--------------+-----------------+
| parent       | child           |
+--------------+-----------------+
| Ceiling Fans | Decorative fans |
+--------------+-----------------+

这可能是非常基本的问题，我从查询中得到了我想要的，我只是想知道有什么办法可以缩短这个查询

我正在寻找一种可以使用以下查询的方法

select ccmcatrefid1 as parentId ,ccmcatrefid2 as childId from cmsccm 
where ccmrefid = 141;

而不是分别查询 childId 和 parentId。

-------------------------------------------- ------表结构

1 cmscat (category table)
it have (catrefid , catname)

2 cmsccm (category mapping table, it maps category as parent and child)
if have (ccmrefid ,ccmcatrefid1, ccmcatrefid2)
where ccmcatrefid1 and ccmcatrefid2 are id of cmscat table(catrefid)

Answer 1

使用三向连接:

SELECT a.catname AS parent, b.catname AS child
FROM cmsccm AS c
JOIN cmscat AS a ON a.catrefid = c.ccmcatrefid1
JOIN cmscat AS b ON b.catrefid = c.ccmcatrefid2
WHERE c.ccmrefid = 141