Mysql syntax difficulty...

[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?

[LazyJones]

Looks alright for me, what is the problem?

[fenway]

The problem is that there's no SQL query in sight! To do this is one statement, you'll have to use subquries (4.1+):

 

SELECT r.code, d.datab_name
FROM reference AS r 
,protein_reference AS pr 
,`database` AS d 
WHERE pr.protein_id = ( SELECT reference_id FROM reference WHERE code = 'AAAA' ) 
AND r.code <> 'AAAA' 
AND pr.reference_id = r.reference_id 
AND d.datab_id = r.datab_id;

 

BTW, this doesn't require the use of table1, though it could be easly added to the query. Hope that helps.