Jump to content


Photo

Mysql syntax difficulty...


  • Please log in to reply
2 replies to this topic

#1 ktsirig

ktsirig
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts

Posted 04 January 2006 - 09:15 AM

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?

#2 LazyJones

LazyJones
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 04 January 2006 - 09:43 AM

Looks alright for me, what is the problem?

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 04 January 2006 - 07:19 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users