SQL error Mysql 4 to 5 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL error Mysql 4 to 5

Author

Topic

bacarndiaye
Starting Member

3 Posts

Posted - 2011-11-15 : 04:11:55

Hi i have error SQl when i have migrate my website to Mysql 4 to 5, all table is corret but i have this error

[SQL Err] SELECT
                CM_ArticleLevel1.Name as Level1Name,
                CM_ArticleLevel1.ArticleLevel1ID as ArticleLevel1ID,
                CM_Article.ArticleID,
                fTitle.FieldValue as Title,
                fSDescription.FieldValue as SDescription,
                fDate.FieldValue as Date,
                fBDescription.FieldValue as BDescription,
                fEnable.FieldValue as Enable,
                fHome.FieldValue as Home,
                fRank.FieldValue as Rank,
                fPicture.FieldValue as Picture

FROM CM_Article, CM_ArticleLevel1
LEFT JOIN CM_ArticleFieldValue_Varchar fTitle      ON CM_Article.ArticleID = fTitle.ArticleID and fTitle.FieldID = 1
LEFT JOIN CM_ArticleFieldValue_Int fRank      ON CM_Article.ArticleID = fRank.ArticleID and fRank.FieldID = 18
LEFT JOIN CM_ArticleFieldValue_Text fSDescription  ON CM_Article.ArticleID = fSDescription.ArticleID and fSDescription.FieldID = 2
LEFT JOIN CM_ArticleFieldValue_Text fBDescription  ON CM_Article.ArticleID = fBDescription.ArticleID and fBDescription.FieldID = 5
LEFT JOIN CM_ArticleFieldValue_Varchar fPicture    ON CM_Article.ArticleID = fPicture.ArticleID and fPicture.FieldID = 7
LEFT JOIN CM_ArticleFieldValue_Varchar fEnable    ON CM_Article.ArticleID = fEnable.ArticleID and fEnable.FieldID = 6
LEFT JOIN CM_ArticleFieldValue_Varchar fHome    ON CM_Article.ArticleID = fHome.ArticleID and fHome.FieldID = 4
LEFT JOIN CM_ArticleFieldValue_Date fDate    ON CM_Article.ArticleID = fDate.ArticleID and fDate.FieldID = 3

WHERE CM_Article.ModuleID = 1 AND CM_ArticleLevel1.ArticleLevel1ID = CM_Article.ArticleLevel1ID
AND fEnable.FieldValue Like 'Yes'
AND fHome.FieldValue Like 'Yes'
ORDER BY Rank,Date DESC
LIMIT 0, 4
 ** ErrMess:Unknown column 'CM_Article.ArticleID' in 'on clause'

Help me

Sachin.Nand

2937 Posts

Posted - 2011-11-15 : 04:29:15

[code]
SELECT
CM_ArticleLevel1.Name as Level1Name,
CM_ArticleLevel1.ArticleLevel1ID as ArticleLevel1ID,
CM_Article.ArticleID,
fTitle.FieldValue as Title,
fSDescription.FieldValue as SDescription,
fDate.FieldValue as Date,
fBDescription.FieldValue as BDescription,
fEnable.FieldValue as Enable,
fHome.FieldValue as Home,
fRank.FieldValue as Rank,
fPicture.FieldValue as Picture

FROM CM_Article
INNER JOIN CM_ArticleLevel1 ON CM_ArticleLevel1.ArticleLevel1ID = CM_Article.ArticleLevel1ID
LEFT JOIN CM_ArticleFieldValue_Varchar fTitle ON CM_Article.ArticleID = fTitle.ArticleID and fTitle.FieldID = 1
LEFT JOIN CM_ArticleFieldValue_Int fRank ON CM_Article.ArticleID = fRank.ArticleID and fRank.FieldID = 18
LEFT JOIN CM_ArticleFieldValue_Text fSDescription ON CM_Article.ArticleID = fSDescription.ArticleID and fSDescription.FieldID = 2
LEFT JOIN CM_ArticleFieldValue_Text fBDescription ON CM_Article.ArticleID = fBDescription.ArticleID and fBDescription.FieldID = 5
LEFT JOIN CM_ArticleFieldValue_Varchar fPicture ON CM_Article.ArticleID = fPicture.ArticleID and fPicture.FieldID = 7
LEFT JOIN CM_ArticleFieldValue_Varchar fEnable ON CM_Article.ArticleID = fEnable.ArticleID and fEnable.FieldID = 6
LEFT JOIN CM_ArticleFieldValue_Varchar fHome ON CM_Article.ArticleID = fHome.ArticleID and fHome.FieldID = 4
LEFT JOIN CM_ArticleFieldValue_Date fDate ON CM_Article.ArticleID = fDate.ArticleID and fDate.FieldID = 3

WHERE CM_ArticleLevel1.ArticleLevel1ID = CM_Article.ArticleLevel1ID
AND fEnable.FieldValue Like 'Yes'
AND fHome.FieldValue Like 'Yes'
ORDER BY Rank,Date DESC
LIMIT 0, 4
[/code]

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 04:30:09

this is MS SQL Server forum. so people here wont be having much expertise in MySQL. So please try your luck in some MySQL forums like www.dbforums.com

As a sidenote, seeing your error if feels like you dont have column ArticleID in CM_Article table. so please check it first

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-15 : 04:33:41

visakh is right but this:
FROM CM_Article, CM_ArticleLevel1
seems to be the problem
Just try the solution given by Sachin.Nand because there is a correct INNER JOIN instead of joining by using table,table

No, you're never too old to Yak'n'Roll if you're too young to die.

bacarndiaye
Starting Member

3 Posts

Posted - 2011-11-15 : 04:56:20

thank Sachin.Nand, it's working now

quote:

Originally posted by Sachin.Nand


SELECT
                CM_ArticleLevel1.Name as Level1Name,
                CM_ArticleLevel1.ArticleLevel1ID as ArticleLevel1ID,
                CM_Article.ArticleID,
                fTitle.FieldValue as Title,
                fSDescription.FieldValue as SDescription,
                fDate.FieldValue as Date,
                fBDescription.FieldValue as BDescription,
                fEnable.FieldValue as Enable,
                fHome.FieldValue as Home,
                fRank.FieldValue as Rank,
                fPicture.FieldValue as Picture

FROM CM_Article
INNER JOIN CM_ArticleLevel1 ON CM_ArticleLevel1.ArticleLevel1ID = CM_Article.ArticleLevel1ID
LEFT JOIN CM_ArticleFieldValue_Varchar fTitle      ON CM_Article.ArticleID = fTitle.ArticleID and fTitle.FieldID = 1
LEFT JOIN CM_ArticleFieldValue_Int fRank      ON CM_Article.ArticleID = fRank.ArticleID and fRank.FieldID = 18
LEFT JOIN CM_ArticleFieldValue_Text fSDescription  ON CM_Article.ArticleID = fSDescription.ArticleID and fSDescription.FieldID = 2
LEFT JOIN CM_ArticleFieldValue_Text fBDescription  ON CM_Article.ArticleID = fBDescription.ArticleID and fBDescription.FieldID = 5
LEFT JOIN CM_ArticleFieldValue_Varchar fPicture    ON CM_Article.ArticleID = fPicture.ArticleID and fPicture.FieldID = 7
LEFT JOIN CM_ArticleFieldValue_Varchar fEnable    ON CM_Article.ArticleID = fEnable.ArticleID and fEnable.FieldID = 6
LEFT JOIN CM_ArticleFieldValue_Varchar fHome    ON CM_Article.ArticleID = fHome.ArticleID and fHome.FieldID = 4
LEFT JOIN CM_ArticleFieldValue_Date fDate    ON CM_Article.ArticleID = fDate.ArticleID and fDate.FieldID = 3

WHERE CM_ArticleLevel1.ArticleLevel1ID = CM_Article.ArticleLevel1ID
AND fEnable.FieldValue Like 'Yes'
AND fHome.FieldValue Like 'Yes'
ORDER BY Rank,Date DESC
LIMIT 0, 4

PBUH

bacarndiaye
Starting Member

3 Posts

Posted - 2011-11-15 : 05:02:35

With MySQL 5.0 the procedure of joined(contacted) requests ( JOIN) changed to follow the standards more correctly. In certain consequences requests which worked well before turn(return) an error " on 1054-Unknown column ' xxx ' in ' one clause '.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources