Jump to content

Archived

This topic is now archived and is closed to further replies.

ktsirig

Mysql syntax difficulty...

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.