List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:July 11 2001 5:43pm
Subject:Re: Multiple column value
View as plain text  
t2 has a line for each of the multiple IDs

MYKEY  ID
----   -------
key1   IM34456
key1   IM34457
key1   IM34458
key2   IM34457
key2   IM34567

...

t1 would have lines like ( using your fields instead of name,addr,city )

MYKEY NAME  ADDR  CITY
----- ----- ----- -----
key1  name1 addr1 city1
key2  name2 addr2 city2


select t1.MYKEY, ID, NAME, ADDR, CITY
from t1,t2 where t1.mykey=t2.mykey and  ID='IM34457'

returns

key1 IM34457 name1 addr1 city1
key2 IM34457 name2 addr2 city2
...


MikeBlezien wrote:

> Gerald,
> 
> You lost me on the second table, t2?? The t1 table would store a 'mykey' and the
> multiple values(ID's) and table t2 would have the reference 'mykey' to table t1
> and the ID column,... this is where you loose me??
> 
> 
> 
> 
>>> On Wed, 11 Jul 2001 08:26:11 -0500, Gerald Clark
> <gerald_clark@stripped>   wrote:
>> 
> 
>>> Use another table to hold these values.
>>> This table contains the ID that you are searching on and the key
>>> to the other table that contains the rest of the data.
>>> 
>>> t1 : mykey , field1, field2, field3, field4 .....
>>> 
>>> t2 : mykey , ID
>>> 
>>> 
>>> Select * from t1,t2 where t1.mykey = t2.mykey and t2.ID="IM34485";
>>> 
>>> MikeBlezien wrote:
>>> 
>>> 
>>>> Hi All,
>>>> 
>>>> Quick question. We need to store a multiple value in a table column as we
> don't
>>>> know how many values at the time of submission there could be. The column
> would
>>>> be storing a group of values something like this:
>>>> 
>>>> Column 'ID' 
>>>> IM34456,IM34485,IM34478 
>>>> 
>>>> or it could be more or less. So if we put the data into one column what
> would be
>>>> the best way to extract the row if there's is a match of one of the
> values in
>>>> the column. 
>>>> 
>>>> We need to build a query to select a row that matches one of the values.
> They
>>>> would enter a single value like IM34485 into a form, then I need to build
> a
>>>> query that will check all values in the ID column so we can then extract
> the
>>>> entire row if one of the ID values match.
>>> 
> 
> Mike(mickalo)Blezien
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Thunder Rain Internet Publishing
> Providing Internet Solutions that work!
> http://www.thunder-rain.com
> Tel: 1(225)686-2002
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


-- 
Gerald L. Clark
gerald_clark@stripped

Thread
Multiple column valueMike<mickalo>Blezien11 Jul
  • RE: Multiple column valueRodrigo Zerlotti11 Jul
    • Re: Multiple column valueMike<mickalo>Blezien11 Jul
      • RE: Multiple column valueRodrigo Zerlotti11 Jul
  • Re: Multiple column valueGerald Clark11 Jul
    • Re: Multiple column valueMike<mickalo>Blezien11 Jul
  • Re: Multiple column valueGerald Clark11 Jul
    • Re: Multiple column valueMike<mickalo>Blezien11 Jul