1. SQL How To
If one of my fields in a database is a sequence like {1,2,5} and those numbers
are references to unique IDs in another table, how would I get a field from
the other table using those reference IDs (that is, what's the SQL)?
For instance
person table (1 record):
unique_id:1
name:cklester
fruits:{1,2,5}
fruits table (5 records):
unique_id:1
name:apple
unique_id:2
name:orange
unique_id:3
name:pear
unique_id:4
name:banana
unique_id:5
name:kiwi
So my query would return {"apple","orange","kiwi"}
Should I do away with the sequence field and create a new table like so:
prefs table (3 records):
unique_id:1
person:1
fruit_pref:1
unique_id:2
person:1
fruit_pref:2
unique_id:3
person:1
fruit_pref:3
Thanks!
-=ck
"Programming in a state of EUPHORIA."
http://www.cklester.com/euphoria/
2. Re: SQL How To
On 9/3/05, cklester <guest at rapideuphoria.com> wrote:
> Should I do away with the sequence field and create a new table like so:
>
> prefs table (3 records):
> unique_id:1
> person:1
> fruit_pref:1
>
> unique_id:2
> person:1
> fruit_pref:2
>
> unique_id:3
> person:1
> fruit_pref:3
Yes, you should - that's a staple of relational databases.
--
MrTrick
----------
3. Re: SQL How To
Patrick Barnes wrote:
>
> On 9/3/05, cklester <guest at rapideuphoria.com> wrote:
> > Should I do away with the sequence field and create a new table like so:
> >
> > prefs table (3 records):
> > unique_id:1
> > person:1
> > fruit_pref:1
> >
> > unique_id:2
> > person:1
> > fruit_pref:2
> >
> > unique_id:3
> > person:1
> > fruit_pref:3
>
> Yes, you should - that's a staple of relational databases.
Yeah, I figured as much... went ahead and did it... so... what's the SQL
query for gettin' that list of fruits?! :)
-=ck
"Programming in a state of EUPHORIA."
http://www.cklester.com/euphoria/
4. Re: SQL How To
One thing... I always use as a database convention: Each table has a
primary key called 'id'.
Any foreign key would be '%tablename%_id', for instance person_id or
fruit_id... if the foreign key is self referential, then something
like parent_id or similar works, depending on the relationship.
So, table structure:
Table person:
fields: id, name
Table fruit:
fields: id, name, colour
Table person_fruit_prefs:
fields: id, person_id, fruit_id
So lets say you want to retrieve all the names fruits that a
particular person likes... lets say his id is 42:
select fruit.name from=20
person_fruit_prefs inner join fruit=20
on person_fruit_prefs.fruit_id = fruit.id=20
where person_fruit_prefs.person_id = 42;
Make sense?
To find a list of all the people who like red fruit:
select person.name=20
from person inner join person_fruit_prefs=20
on person.id = person_fruit_prefs.person_id=20
where person_fruit_prefs.fruit_id in (select fruit.id from fruit where
fruit.colour = 'red');
Hope that helps...
On 9/3/05, cklester <guest at rapideuphoria.com> wrote:
> Yeah, I figured as much... went ahead and did it... so... what's the SQL
> query for gettin' that list of fruits?! :)
--
MrTrick
----------
