SQL-如何从同一列中选择多个表并联接多个行? [英] SQL - how to SELECT multiple tables and JOIN multiple rows from the same column?

问题描述

如何从同一列中选择多个表并联接多个行?

How to SELECT multiple tables and JOIN multiple rows from the same column?

它不返回painting.id_group，painting.id_type和painting.id_location. 我确定此SQL代码段的语法错误.

It does not return painting.id_group, painting.id_type and painting.id_location. I'm sure this SQL snippet has wrong syntax.

SELECT painting.id, painting.order, painting_en.url, id_portfolio.en
FROM painting
LEFT JOIN painting_en
USING ( id ) 
LEFT JOIN id_portfolio ON id_portfolio.id = painting.id_group
AND id_portfolio.id = painting.id_type
AND id_portfolio.id = painting.id_location
WHERE painting_en.url='2011-name3'
LIMIT 1

更新: 它显示Showing rows 0 - 0 ( 1 total, Query took 0.0004 sec). 删除后

Update: It shows Showing rows 0 - 0 ( 1 total, Query took 0.0004 sec). After removing

AND id_portfolio.id = painting.id_type
AND id_portfolio.id = painting.id_location

它返回:

id        | order | url        |en
----------+-------+------------+-----
09518709  | 4     | 2011-name3 | NULL

但是我需要返回:

id        | order | url        | group   | type          | location
----------+-------+------------+---------+---------------+---------
09518709  | 4     | 2011-name3 | realism | oil on canvas | US

推荐答案

您可以多次连接至同一表，而只需提供不同的别名即可.

You can join to same table multiple times you just need to give different aliases

SELECT painting.id, 
       painting.order, 
       painting_en.url, 
       `group`.en AS `group`, 
       TYPE.en AS type, 
       location.en AS location
FROM   painting 
       LEFT JOIN painting_en 
       USING (id) 
       LEFT JOIN id_portfolio `group` 
         ON `group`.id = painting.id_group 
       LEFT JOIN id_portfolio TYPE 
         ON TYPE.id = painting.id_type 
       LEFT JOIN id_portfolio location 
         ON location.id = painting.id_location 
WHERE  painting_en.url = '2011-name3' 
LIMIT  1 

这篇关于SQL-如何从同一列中选择多个表并联接多个行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！