MySQL inner join same table based on field value

Question

I have a table structure as follows:

|id|record_id|key    |vale
|1 |2        |email  |xx@xxx.com    
|2 |2        |name   |John    
|3 |2        |tel    |676776    
|4 |3        |email  |tt@rrt.com    
|5 |3        |name   |James    
|6 |3        |tel    |7676767     
|7 |4        |emal   |5656@rtrt.com   

I'm trying to display the "email" and "name" that match the record_id from the same table.

My attempted cfquery is as follows, but sadly I'm not getting any success.

<cfquery name="getStatic" datasource="#session.odbcname#">
    SELECT  *
    FROM    field_values n
    INNER JOIN field_values e
    ON e.key
    AND e.field_values.key = 'name'
    WHERE n.field_values.record_id = e.field_values.record_id
</cfquery> 

Any ideas as to where I'm going wrong would be much appreciated.

Answer 1

Try this:

select t1.value email, t2.value name
from field_values t1
inner join field_values t2
on t1.record_id = t2.record_id
and t1.key = 'email'
and t2.key = 'name';