definitely not the prettiest data! To work with what you have you can:
1. create a temp table and load the data in a normalized fashion. If
there isn't a ton of data this might be an easy option. Create a
stored procedure that does the DDL and load. This allows you and your
users to write simple SQL statement to access the data.
2. create a stored function passing the people.id column value as a
parameter. and return a concatenated string of the pets. Use a select
statement to access the function. Of course you will still need to
parse the return string to get the data in a normalized / readable
form.
3. You could also use a pl/sql table within a stored package.
kittycatangel_at_hotmail.com (Angel Cat) wrote in message news:<9b08c59d.0301302221.6c859855_at_posting.google.com>...
> Hello!
>
> I have 2 tables that I need to join together in a SELECT but it
> doesn't seem easy
>
> PEOPLE table
> ID int
> NAME varchar
>
> PETS table
> PETTYPE varchar
> PEOPLE_IDS varchar
>
> The PEOPLE table links to the PETS with the PEOPLE's IDs separated by
> semicolons!
>
> So, PETS will have records like this
> PET PEOPLE_IDS
> Cat 1;34;35;88;101;122
> Dog 4;33;34;
>
> And PEOPLE will have records like this:
> ID NAME
> 1 John
>
> So, John has 1 cat, no dogs. In SQL statement, how can I retrieve what
> pets John has? Subqueries doesn't seem to allow the 'like' clause..
>
> I need a SELECT like this:
>
> SELECT ID FROM PEOPLE WHERE NAME = "JOHN" AND ID LIKE "%(SELECT
> PEOPLE_IDS FROM PETS' %"
>
> But the syntax doesn't work like that. :confused: I also need this
> done in 1 statement, no DECLARE variables in 2 lines
>
> Any help is appreciated!
Received on Fri Jan 31 2003 - 13:51:06 CST
