dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Mysql syntax problem

  1. #1
    Join Date
    Sep 2005
    Posts
    73

    Mysql syntax problem

    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:
    Code:
    [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?
    Last edited by Ejaz; January 4th, 2006 at 05:14 AM. Reason: Tags added

  2. #2
    Join Date
    Apr 2005
    Posts
    27

    Wink Re: Mysql syntax problem

    modify this and try....

    select CONCAT( c.code,'[', data.name,']' ) from ( select code, data_id from ref where ref_id in ( select ref_id from protein_ref where protein_id in ( select ref_id from ref where ref.code = 'AAAA') and ref_id not in ( select ref_id from ref where ref.code = 'AAAA'))) as c, data where c.data_id = data.data_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)