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?

bpat1434

I don't think column names can have "." in them, so you may want to change them to "_" instead. That could cause huge problems there.

You can do this in multiple queries. I'm not sure about how to do it with one. I know it has to do with joins and such, but I'm not the foremost authority on it. Maybe Sxooter will get around here and answer this for you.

Well, this gets you really close:

SELECT a.code AS code, c.datab_name AS name, b.reference_id AS id   

FROM `reference` AS a 
INNER JOIN `protein_reference` AS b ON a.reference_id=b.protein_id 
INNER JOIN `database` AS c ON a.datab_id=c.datab_id 
WHERE a.code='$user_input'

That query outputs this:

AAAA [Yale] 1
AAAA [Yale] 2
AAAA [Yale] 4

Not sure what's going on. Something with the joins. Like I said, I'm not the foremost authority, but that's all I can help you with. THe b.reference_id AS id was just to make sure that I was actually getting different values.

Only thing it doesn't wanna do is write the proper names... not sure why....

~Brett