Jump to content

Mysql syntax difficulty...


ktsirig

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?

Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.