List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 17 2005 2:32pm
Subject:Re: how to arrange my table in another direction
View as plain text  
/>but,the query cannot run on my mysql 4.0.23

/See http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html for how
to get round the unavailability of subqueries before version 4.1.

PB

-----


维斯 苏 wrote:

>thank you 
>but after i read it, I try the second example
>Pivot table using a JOIN
>Problem: You have table user_class(user_id int, class_id char(20), class_value
> char(20)) with these rows: 
>user_id  class_id   class_value
>1        firstname  Rogier
>1        lastname   Marat
>2        firstname  Jean
>2        lastname   Smith
>and you wish create this resultset: 
>user_id  firstname  lastname
>1        Rogier     Marat
>2        Jean       Smith
>
>This query accomplishes the required pivot table via an INNER JOIN: 
>SELECT 
>  t1.user_ID,
>  class_value AS firstname,
>  t2.lastname 
>FROM user_class AS t1
>  INNER JOIN (
>    SELECT 
>      user_ID,
>      class_value AS lastname 
>    FROM user_class
>    WHERE class_id='lastname'
>    ) AS t2 
>    ON t1.user_ID=t2.user_ID AND t1.class_id='firstname'
>but,the query cannot run on my mysql 4.0.23
>it returns:
>
>SQL 查询 :  
>
>SELECT t1.user_ID, class_value AS firstname, t2.lastname
>FROM user_class AS t1
>INNER JOIN ( 
>SELECT user_ID, class_value AS lastname
>FROM user_class
>WHERE class_id = 'lastname'
>) AS t2 ON t1.user_ID = t2.user_ID AND t1.class_id = 'firstname'
>LIMIT 0 , 30 
>
>
>MySQL 返回:
>
>#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to
> your MySQL server version for the right syntax to use near 'SELECT user_ID, class_value AS
> lastname
>FROM user_class
>WHERE c 
>
>How can i fix it? and thank you for your help.
>
>		
>---------------------------------
>DO YOU YAHOO!?
>  雅虎免费G邮箱-中国第一绝无垃圾邮件骚扰超大邮箱 
>  
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 8/15/2005
>  
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.11/74 - Release Date: 8/17/2005
Thread
how to arrange my table in another direction维斯 苏16 Aug
  • Re: how to arrange my table in another directionGleb Paharenko16 Aug
  • Re: how to arrange my table in another directionGleb Paharenko16 Aug
    • Re: how to arrange my table in another direction维斯 苏17 Aug
  • Re: how to arrange my table in another directionPeter Brawley17 Aug
re:how to arrange my table in another direction维斯 苏17 Aug
  • Re: how to arrange my table in another directionPeter Brawley17 Aug