>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