CODEWITHSUNDEEP
mysql
SIOIV
SIOIV

Reputation: 1

What`s wrong in MySQL query?

SELECT n.nid, n.title, c.field_news_date_value, 
       c.field_news_short_text_value, c.field_news_short_text_format 
FROM node n, term_data m,  term_node p 
INNER JOIN content_type_news c 
   ON c.nid = n.nid AND c.vid = n.vid 
WHERE n.type='news' AND n.status=1 
ORDER BY c.field_news_date_value DESC.

I am getting an error

Unknown column 'n.nid' in 'on clause'.

Upvotes: 0

Views: 91

Answers (3)

Vinoth Babu
Vinoth Babu

Reputation: 1

You need to use AS in the query. Please use the following query :

SELECT 
 n.nid, n.title, 
 c.field_news_date_value, 
 c.field_news_short_text_value, 
 c.field_news_short_text_format 
FROM 
 node as n, term_data as m,  term_node as p 
INNER JOIN 
 content_type_news c ON c.nid = n.nid AND c.vid = n.vid 
WHERE 
 n.type='news' 
 AND n.status=1 
ORDER BY 
 c.field_news_date_value DESC

Upvotes: 0

xkeshav
xkeshav

Reputation: 54016

I think query must be like this. ( assuming there must be columns, name nid and vid in both tables)

Note : it is ambiguous to use use multiple table in FROM clause when we are using JOIN

SELECT n.nid, n.title, c.field_news_date_value, 
       c.field_news_short_text_value, c.field_news_short_text_format 
FROM node n
INNER JOIN content_type_news c 
ON c.nid = n.nid AND c.vid = n.vid 
WHERE n.type='news' AND n.status=1 
ORDER BY c.field_news_date_value DESC.

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try this:

SELECT n.nid, n.title, c.field_news_date_value, c.field_news_short_text_value, c.field_news_short_text_format 
FROM term_data m, term_node p, node n INNER JOIN content_type_news c 
ON c.nid = n.nid AND c.vid = n.vid 
WHERE n.type='news' AND n.status=1 
ORDER BY c.field_news_date_value DESC

Basically, moved node n near to the INNER JOIN operator.

Upvotes: 0

Related Questions