Evening Red October 2009,

> ...

> 1 ^ 185 ^ RALF JOEY FRED ^ BRUTUS
> 1 ^ 105 ^ KARL JACOB NEWTON ^ SNACKER
> 1 ^ 973 ^ MARLEEN MUFFY ABE ^ LUVER POPEYE TANGO
> 2 ^ 243 ^ BAXTER MILTON MARY ^ RIPPER JOEY TOMMY
> 2 ^ 239 ^ ABIGAIL HARMONY ^ SPOT MUFFY TIPPY
> 2 ^ 982 ^ KEVIN PETE PAT ^ THOR TINKLES KELSEY
>
> I need an SQL statement that will return all records where at least one human name is found in the pet names field (not the other way around). In this case the only records that qualify are:

Are you saying that you want an SQL statement that will go through every
"list of human names" extracting each name, and for each one, go through
every "list of pet names" to see if there's a match?

> 2-243 (JOEY)
> 2-239 (MUFFY)
>
> Anyone got any ideas? Every time I attempt this, it gets overly complicated.

I don't have an answer at the moment, sorry. I rather suspect you might
have to write a procedure to do this, as opposed to a single plain SQL
statement. I'm afraid that this is a nightmare caused by having a
non-normalised table.

Please excuse the following eRant.

You shouldn't have lists of anything in a single column of a table. You
shouldn't have columns named pet_name_1, pet_name_2 etc because that's
not normalised either.

You will have other problems as well, if there are more names than can
fit into your space delimited human and/or pet names. The names of
humans and/or pets should be extracted to a separate table, possibly of
the following format:

USR_ID INTEGER
ID_NMBR INTEGER
PET_OR_HUMAN VARCHAR(1)
A_NAME VARCHAR(50)

The first two columns would be a foreign key back to the appropriate
primary key in the main table, and the value in A_NAME would be *one
single* name, of a pet or human, as indicated by the PET_OR_HUMAN column
- which would of course have a constraint to ensure only the two values
expected - say P or H - were indeed allowed.

Your main table would be redefined as:

USR_ID INTEGER (primary key, field 1)
ID_NMBR INTEGER (primary key, field 2)

I assume that there are other columns not mentioned.

Now it's far easier to write a statement to extract wrongly stored
names. Assuming it was needed of course!

As Ann often says, good luck!


Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767