Mysql syntax problem

[ktsirig]

Hi everybody and Happy New Year!
I have been dealing with this problem of my biology class lesson since yesterday,
I believe it's some silly mistake I am making.
I have these tables:

[tabel1:protein]
protein_id	protein.name		
1		PROTEIN_1	
2		PROTEIN_2	
3		PROTEIN_3
############################################
[table2:protein_reference]
protein_id[FK]	reference_id[FK]
1		1
1		2
1		4
2		3
2		6
3		5
3		7
###############################################
[table3:reference]
reference_id	datab_id[FK]	code	
1		1		AAAA
2		2		BBBB
3		2		CCCC
4		3		DDDD
5		1		EEEE
6		3		FFFF
7		1		GGGG
##############################################
[table4:database]
datab_id	datab.name
1		Yale
2		Oxford
3		Cambridge
##############################################

If the user gives me code AAAA as input,
I want to write an SQL statement that will retrieve all the other codes from table3
and all datab.name from table4 that belong to the same protein,
that is: BBBB[+Oxford], DDDD[+Cambridge].

I hope it is not confusing..
The course that SQL must follow is:
STEP1: Code AAAA is given from user
STEP2: go to table2 and see(using reference_id) that protein_id#1 has also reference_id#2 +reference_id#4
STEP3: go to table3 and see which datab_id are placed in codes BBBB + DDDD
STEP4: go to table4 and see (using datab.name) that the reffering databases are those of Oxford(datab_id#2)
and Cambridge(datab_id#3)
STEP5: print => BBBB[Oxford]
DDDD[Cambridge]


Any help?

[manoj.m]

modify this and try....

select CONCAT( c.code,'[', data.name,']' ) from ( select code, data_id from ref where ref_id in ( select ref_id from protein_ref where protein_id in ( select ref_id from ref where ref.code = 'AAAA') and ref_id not in ( select ref_id from ref where ref.code = 'AAAA'))) as c, data where c.data_id = data.data_id