From: Peter Brawley Date: August 17 2005 2:32pm Subject: Re: how to arrange my table in another direction List-Archive: http://lists.mysql.com/mysql/187960 Message-Id: <43034A77.2030007@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-43034A7A028C=======" --=======AVGMAIL-43034A7A028C======= Content-Type: multipart/alternative; boundary=------------060609070408040701010002 --------------060609070408040701010002 Content-Type: text/plain; charset=GB2312 Content-Transfer-Encoding: 8bit />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 > > --------------060609070408040701010002 Content-Type: text/html; charset=GB2312 Content-Transfer-Encoding: 8bit >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

--------------060609070408040701010002-- --=======AVGMAIL-43034A7A028C======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-43034A7A028C=======--