我需要一个查询来将某些mySQL数据库列放到一行中

Question

我需要加入这个mySQL表：

TABLE1

id  pagetitle
1   remodeling
2   handywork
3   aesthetics

使用这一条：

TABLE2

id  contentid  tmplvarid  value
1   1          1          Jaime
2   1          2          img/remodeling.jpg
3   2          1          Alex
4   2          2          img/handywork.jpg
5   3          1          Karla
6   3          2          img/aesthetics.jpg

要输出以下内容，请执行以下操作：

id  pagetitle   author  image
1   remodeling  Jaime   img/remodeling.jpg
2   handywork   Alex    img/handywork.jpg
3   aesthetics  Karla   img/aesthetics.jpg

注意: Table1和Table2的关系是: Table1.id = Table2.contentid

如果有帮助，...tmplvarid 1是作者，tmplvarid2是图像

我可以使用什么SQL查询来完成此任务？

Answer 1

select t1.id,
       t1.pagetitle,
       (select value from TABLE2 where contentid = t1.id and tmplvarid = 1) as author,
       (select value from TABLE2 where contentid = t1.id and tmplvarid = 2) as image
  from TABLE1 t1