CODEWITHSUNDEEP
mysqlsqldatabaseleft-join
user823871
user823871

Reputation:

Strange: MySQL Left Join, "unknown column"; the same leftjoin doesn't work in multiple-table query

I'm trying to run a select query, which is the following:

SELECT * FROM pages, c_item_category cc
LEFT JOIN pages_tr ON (pages.page_id = pages_tr.page_id AND lang_id = 2)
LEFT JOIN users ON (pages.page_author = users.u_id)
WHERE (pages.page_id = cc.item_id AND cc.cat_id = 7)
  AND (page_date >= 1317420000 AND page_date <= 1320101999)
  AND (page_showinfos = 1)
ORDER BY page_date ASC LIMIT 0,10

But I get this error: Unknown column 'pages.page_id' in 'on clause'

Why is that? I can confirm that I have a column named 'page_id' in 'pages' :)

But, when I do a single-table query with the same left joins, it works well:

SELECT * FROM pages
LEFT JOIN pages_tr ON (pages.page_id = pages_tr.page_id AND lang_id = 2)
LEFT JOIN users ON (pages.page_author = users.u_id)
WHERE (page_date >= 1317420000 AND page_date <= 1320101999)
  AND (page_showinfos = 1)
ORDER BY page_date ASC LIMIT 0,10

What is my mistake in the first query? :\

Upvotes: 2

Views: 2414

Answers (1)

Kevin Horgan
Kevin Horgan

Reputation: 1580

Try it this way instead.

SELECT *
FROM pages
LEFT JOIN pages_tr ON (pages.page_id = pages_tr.page_id AND lang_id = 2)
LEFT JOIN users ON (pages.page_author = users.u_id),
c_item_category cc
WHERE (pages.page_id = cc.item_id AND cc.cat_id = 7)
  AND (page_date >= 1317420000 AND page_date <= 1320101999)
  AND (page_showinfos = 1)
ORDER BY page_date ASC LIMIT 0,10

Upvotes: 2

Related Questions