converting from oracle sql

[Donovan Makund]

I have a table with structure/values as follows:

TABLE: FIELD_VALUES
ID,USER_ID,FIELD_ID,VALUE
1, 60, 20, '1986'
2, 60, 21, 'Marist Brothers' High School'
3, 61, 20, '1986'
4, 61, 21, 'Marist Brothers' High School'

I need to create a sql that shows which users shared the same year and school.

The following sql for oracle works:


SELECT user_id,school,year
FROM
(SELECT nvl(n.user_id,y.user_id) user_id,n.value school, y.value year
FROM
(SELECT user_id,value
FROM FIELD_VALUES
WHERE field_id = 20
) y
FULL OUTER JOIN
(SELECT user_id,value
FROM FIELD_VALUES
WHERE field_id = 21
) n ON y.user_id = n.user_id
) t
WHERE t.year = ‘1986‘
and t.school = ‘Marist Brothers‘‘ High School‘
/

I have been trying to convert this to MySQL but with no luck so far. I found out that there is no OUTER JOIN feature in MySQL. What else can I do?



Edited 2 time(s). Last edit at 12/10/2008 12:14PM by Donovan Makund.