syntax error

[doddo]

hi, please could someone explain the reason why the following command is throwing the following syntax error:

You have an error in your SQL syntax near 'SELECT [Category] FROM [Raw] As Tmp GROUP BY [Category],[Individual_ID] HAVING C' at line 3


SELECT DISTINCTROW Raw.Category, Raw.Individual_ID, Raw.Key, Raw.Lane, Raw.Peak1, Raw.Peak2, Raw.Peak1_Real, Raw.Peak2_Real, Raw.Peak1_Intensity, Raw.Peak2_Intensity, Raw.Overflow, Raw.Filename
FROM Raw
WHERE (((Raw.Category) In (SELECT [Category] FROM [Raw] As Tmp GROUP BY [Category],[Individual_ID] HAVING Count>1 And [Individual_ID] = [Raw].[Individual_ID])) AND ((Raw.Individual_ID) Not Like "control%" And (Raw.Individual_ID) Not Like "water&quot)
ORDER BY Raw.Category, Raw.Individual_ID

I cannot for the life of me figure it out!

Thanks in advance
Rich

 

[devnull22]

what version of mySQL are you using? Earlier versions don't support inner SELECT statements. I'm not even sure if the lastest one does either , you'll need to double check that. devnull22

http://www.photoflux.com

http://www.clickEval.com

--
Apparently if you play the Windows NT CD backwards you hear satanic messages. If you think that's bad, play it forwards and it installs Windows NT !

 

[doddo]

Hi, thanks for that, I think you're right. I guess the best thing to do would be to create a temporary table first contiaining the data from the inner select statement.

Am I right in thinking that there is no way of issuing mutiple queries within one statement? i.e i couldn't create the table first and then perform the query afterwards all in one go?

thanks
Rich

 

[devnull22]

check this out :

http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Missing_Sub-selects

maybe it'll give you some ideas.

cheers devnull22

http://www.photoflux.com

http://www.clickEval.com

--
Apparently if you play the Windows NT CD backwards you hear satanic messages. If you think that's bad, play it forwards and it installs Windows NT !

 

[doddo]

Thanks alot. i was just going to attack it with Perl,


cheers
Rich