MySQL Forums
Forum List  »  Oracle

converting from oracle sql
Posted by: Donovan Makund
Date: December 10, 2008 12:11PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
converting from oracle sql
4932
December 10, 2008 12:11PM
2702
December 10, 2008 02:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.