CREATE ... SELECT: Error 1064

[Dabase]

Hi,

I am trying to create a table as follows:

CREATE TABLE `criteria` (
select PGC.PGC_P_ID, P_Title, GROUP_CONCAT(A_ID,'|', C_Title) AS Criteria 
from tblproductsgroupcriteria PGC 
join tblproducts P on PGC_P_ID = P_ID
join tblproductsgroupindustry PGI on PGInd_P_ID = P_ID 
join tblcriteria C on PGC_C_ID = C_ID 
join tblcriteriagroupapplication CGA on C_ID = CGA_C_ID 
join tblapplication A on CGA_A_ID = A_ID 
where (PGC_C_ID = 8 or PGC_C_ID = 19)
and PGInd_I_ID =3 
group by PGC.PGC_P_ID order by P_Title, A_Title
)

I am getting error 1064. Any ideas where I am going wrong?

The version of MySQL that I am running is: 4.1

I am running the query from MySQL Control Center



Thanks
Dabase

 

[Dabase]

Hi,

There was a ';' missing at the end so that the statement looks like:

CREATE TABLE `criteria` (
select PGC.PGC_P_ID, P_Title, GROUP_CONCAT(A_ID,'|', C_Title) AS Criteria 
from tblproductsgroupcriteria PGC 
join tblproducts P on PGC_P_ID = P_ID
join tblproductsgroupindustry PGI on PGInd_P_ID = P_ID 
join tblcriteria C on PGC_C_ID = C_ID 
join tblcriteriagroupapplication CGA on C_ID = CGA_C_ID 
join tblapplication A on CGA_A_ID = A_ID 
where (PGC_C_ID = 8 or PGC_C_ID = 19)
and PGInd_I_ID =3 
group by PGC.PGC_P_ID order by P_Title, A_Title
);

Apologies for the post .

Thanks
Dabase

 

[mbrooks]

First thing I notice that is fishy is

from [i]tblproductsgroupcriteria [b]PGC[/b][/i]

M. Brooks

http://mbrooks.info

 

[Dabase]

Hi mbrooks,

Are you saying that it is fishy because of the way that the alias has been defined. That is usually how I define tables aliases, is there a better way to do it?



Thanks
Dabase

 

[mbrooks]

Don't you define it like this

tblproductsgroupcriteria [b]AS[/b] PGC

M. Brooks

http://mbrooks.info

 

[Dabase]

No because "AS" is optional

Thanks
Dabase

 

[mbrooks]

I'm baffled. Normally error 1064 is when you are using reserved words within your query.
From what I see here that is not the case.

M. Brooks

http://mbrooks.info

 

[Dabase]

The problem was that I was missing a ";" at the end

Thanks
Dabase

 

[guelphdad]

No, that wasn't the problem. Leaving the ; off the end will not produce the error message you reported. Your query will just not run.

 

[Dabase]

Now I am baffled, because after adding the ';', the query ran with no errors

Thanks
Dabase

 

[guelphdad]

I'm baffled. Normally error 1064 is when you are using reserved words within your query.
From what I see here that is not the case.

a 1064 error message can mean many things. I wish there were a set of guidelines to what each message means, I haven't found one.

Among other things, if you run a subquery and you are using a version that doesn't support them, you will get a 1064 message. Same thing if you run a union and it isn't supported.