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