Mysql syntax problem...

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:

[tabel1rotein]
protein_id protein.name
1 PROTEIN_1
2 PROTEIN_2
3 PROTEIN_3
############################################
[table2rotein_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?

I think you're looking for something like ...

Code: Select all

SELECT * from ((protein_reference join protein 
on protein_reference.protein_id = protein.protein_id) join reference 
on protein_reference.reference_id = reference.reference_id ) join database 
on reference.datab_id = database.datab_id 
where code = "AAAA"

which is basically translating your logic into SQL.
I would suggest that you do NOT call a table "database", by the way ...
You might also find this longer article that I wrote helpful.