Re: tricky SQL SELECT - help!

rick wrote:

> 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!

If he's going to go that far why not suggest using DBMS_UTILITY.COMMA_TO_TABLE ...

Any problem can be solved by brute force. But I'm becoming more convinced that this is a student project rather than a serious business issue. To be convinced otherwise I'd need the name of the commercial product designed this way.

Daniel Morgan Received on Fri Jan 31 2003 - 17:48:34 CST