Professional Web Applications Themes

Link Lookup on Two Tables - MySQL

Dear expert, Suppose I have the following two tables: --- TABLE_A ---- +------------------------------------------------------------------ + GO_Term | GO_Id | Gene ID +--------------------------------------------------------------------- biological_process GO:0008150 814629 biological_process GO:0008150 814631 molecular_function GO:0003674 814631 .............................etc .......................... +---------------------------------------------------------------- --- TABLE_B ---- +------------------------------------------------------------------ + Gene_Symbol | Gene ID +--------------------------------------------------------------------- AT2G01050 814629 AT2G01031 814631 PF14_0640 812222 ..............................etc .......................... +---------------------------------------------------------------- How can one construct a sql command so that: 1. Given GO_Term or GO_Id as Input 2. Obtain their respective Gene_Id 3. For each their Gene_Id finally return their Gene_Symbol. Note that in Table A, the same GO_Term/GO_Id may contain different Gene_ID. Thanks and hope to hear from ...

  1. #1

    Default Link Lookup on Two Tables

    Dear expert,

    Suppose I have the following two tables:

    --- TABLE_A ----
    +------------------------------------------------------------------
    + GO_Term | GO_Id | Gene ID
    +---------------------------------------------------------------------
    biological_process GO:0008150 814629
    biological_process GO:0008150 814631
    molecular_function GO:0003674 814631
    .............................etc ..........................
    +----------------------------------------------------------------

    --- TABLE_B ----
    +------------------------------------------------------------------
    + Gene_Symbol | Gene ID
    +---------------------------------------------------------------------
    AT2G01050 814629
    AT2G01031 814631
    PF14_0640 812222
    ..............................etc ..........................
    +----------------------------------------------------------------

    How can one construct a sql command so that:
    1. Given GO_Term or GO_Id as Input
    2. Obtain their respective Gene_Id
    3. For each their Gene_Id finally return their Gene_Symbol.

    Note that in Table A, the same GO_Term/GO_Id may contain
    different Gene_ID.

    Thanks and hope to hear from you again.

    --
    Regards,
    Edward

    ewijaya Guest

  2. #2

    Default Re: Link Lookup on Two Tables

    ewijaya wrote: 

    Search for a GO_Term:
    SELECT * FROM TABLE_B RIGHT JOIN TABLE_A ON(TABLE_B.`Gene ID`= TABLE_A.`Gene
    ID`) WHERE GO_Term='something';

    Search for a GO_Id:
    SELECT * FROM TABLE_B INNER JOIN TABLE_A ON(TABLE_B.`Gene ID`= TABLE_A.`Gene
    ID`) WHERE GO_Id='something';

    Search in both GO_Term and GO_Id at the same time:
    SELECT * FROM TABLE_B INNER JOIN TABLE_A ON(TABLE_B.`Gene ID`= TABLE_A.`Gene
    ID`) WHERE GO_Term='something' OR GO_Id='something';

    --

    //Aho
    J.O. Guest

  3. #3

    Default Re: Link Lookup on Two Tables

    Hi Aho,

    Thanks so much for your reply.
    Is there a way I can optimize your querry above using Index?
    Especially because the table I have are very large.


    --
    Regards,
    Edward WIJAYA



    On Apr 22, 9:52 pm, "J.O. Aho" <net> wrote:
     


    ewijaya Guest

Similar Threads

  1. reverse lookup/domain lookup in PHP
    By Chris Kranz in forum PHP Development
    Replies: 1
    Last Post: December 18th, 09:41 AM
  2. Lookup Tables in Webservice
    By Michael C# in forum ASP.NET Web Services
    Replies: 2
    Last Post: May 10th, 05:16 PM
  3. [PHP] reverse lookup/domain lookup in PHP
    By Cpt John W. Holmes in forum PHP Development
    Replies: 2
    Last Post: September 11th, 01:30 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139