Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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

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

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

it returns:

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

but I need to return:

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

1 Answer 1

up vote 1 down vote accepted

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 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com?utm_source=stackoverflow.com&amp;utm_medium=site-ui&amp;utm_campaign=footerlink\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a>                   with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.11.28.2054\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </no"], "copyright__copyright__copyright": [" id_portfolio `group` \n         ON `group`.id = painting.id_group \n       LEFT JOIN id_portfolio TYPE \n         ON TYPE.id = painting.id_type \n       LEFT JOIN id_portfolio location \n         ON location.id = painting.id_location \nWHERE  painting_en.url = '2011-name3' \nLIMIT  1 \nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}