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 wrote:
> --- TABLE_A ----
> +------------------------------------------------------------------
> + GO_Term | GO_Id | Gene ID
> +---------------------------------------------------------------------
> --- TABLE_B ----
> +------------------------------------------------------------------
> + Gene_Symbol | Gene ID
> +---------------------------------------------------------------------
> 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.

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

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" <u...@example.net> 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