I need help creating a new database.
It should be something like this:
Table1 Table2
------ ------
t1_ID (int) t2_ID (int)
data1 (text) data1 (text)
data2 (text) data2 (text)
... ...
lnks (??) <-- This should point lnks (??) <-- This should point
to one or more to one or more
rows of the second rows of the first
table. table.
An example:
Table1 Table2
t1_id | data1 | data2 | ... | lnks | t2_id | data1 | data2 |
... | lnks |
------|-------|-------|-----|-----------|
------|-------|-------|-----|-----------|
A01 |TOMATOE|XXX |... |116,118 | 115 |XXX |XXX
|... |A01,A03 |
A02 |RICE |XXX |... |118 | 116 |XXX |XXX
|... |A02,A03,B01|
A03 |BEENS |XXX |... |115,117,118| 117 |XXX |XXX
|... | |
B01 |MILK |XXX |... |117,118 | 118 |XXX |XXX
|... |A01,B01 |
I'm thinking on the type of the field "lnks", if I use an array of
char(20), how can I do the following:
I need to make a query like:
SELECT t1_id FROM Table1 WHERE lnks='117';
and to have an answer like this:
t1_id
-----
A03
B01
And to make a query like:
SELECT Table1.* FROM Table1,Table2 WHERE Table2.t2_id='118';
and to have an answer like this:
t1_id | data1 | data2 | ... | lnks |
------|-------|-------|-----|-----------|
A01 |TOMATOE|XXX |... |116,118 |
B01 |MILK |XXX |... |117,118 |
Which other type could I use for "lnks", maybe just text ("115#117#118")
and make this query:
SELECT t1_id FROM Table1 WHERE lnks like '%117%';
Thanks in advance for any hint.
--
Lic. Geovan Rodriguez Collazo
Center for Genetic Engineering & Biotechnology
Ave. 31 e/ 158 & 190. Cubanacan. Playa.
La Habana. Cuba.
