tricky SQL SELECT

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 31 Jan 2003 15:46:22 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7033EA114@lnewton.leeds.lfs.co.uk>

Afternoon KittyCatAngel,

You are totally up that world famous creek I'm afraid. :o(

But, having said that, ponder over this for a bit :

Your pets table has people_ids in a list, separated by semi colons. Your people table, I assume, can have many many people in it, say up to 999,999,999 just for the hell of it.

You can do 'select pet, people_ids from people where people_ids like '%1%'
to fetch all pets owned by person 1, however, what about person 11 or 21, or 21002 or anyother number with a 1 in it - they will be selected too.

So, you could try 'select pet, people_ids from people where people_ids like '%1;%'
But this time you'll get people numbered 1, 11, 21, 31, or any other 'ending in 1' number.

So, you could really need to use 'select pet, people_ids from people where people_ids like '%;1;%'
Which should work, but what if there is only one person in the list of people having that pet, say person 1 for example, then he won't be selected because the people_ids column doesn't END with a semi-colon. The same will be true for any person whose id ends with a 1 and who is the final person in the list. Additionally, if person 1 is first in the list, then there is no leading semi-colon, so he won't be selected either.

This means that your list of people MUST start and end with a semi-colon. Having got that sorted out, there better not be any spaces allowed between digits !!!

Now, having got that off my chesty parts, we go on :

DROP TABLE people
Table dropped

DROP TABLE pets
Table dropped

CREATE TABLE people(ID NUMBER, NAME VARCHAR2(50)) Table created

CREATE TABLE pets (pet VARCHAR2(20), people_ids VARCHAR2(100)) Table created

INSERT INTO people VALUES (1, 'John')
1 row inserted

INSERT INTO people VALUES (2, 'Jane')
1 row inserted

INSERT INTO people VALUES (3, 'Fred')
1 row inserted

INSERT INTO people VALUES (4, 'Wilma')
1 row inserted

INSERT INTO people VALUES (11, 'Dick')
1 row inserted

INSERT INTO people VALUES (201, 'Mike')
1 row inserted

INSERT INTO people VALUES (311, 'Tom the cabin boy') 1 row inserted

INSERT INTO people VALUES (1410239, 'Uncle Tom Cobbley And All') 1 row inserted

INSERT INTO pets VALUES ('Cat',';2;4;201;') 1 row inserted

INSERT INTO pets VALUES ('Dog',';1;3;311;') 1 row inserted

INSERT INTO pets VALUES ('Snake',';1;2;3;4;1410239;') 1 row inserted

INSERT INTO pets VALUES ('Mouse',';4;1410239;') 1 row inserted

INSERT INTO pets VALUES ('Rat',';11;201;1;4;') 1 row inserted

COMMIT
Commit complete

And here's what you want !

SELECT pet FROM pets
WHERE people_ids LIKE (SELECT '%;'||ID||';%'

                       FROM people
                       WHERE NAME = 'John');

PET                 


--------------------

Dog                 
Snake               
Rat

3 rows selected

Now, assuming you have a support contract with 'The Lone Ranger Database Design Company' (obviously a cowboy outfit), get in touch and tell them never to darken your doorstep agaion :o)

Enjoy.

Regards,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

-----Original Message-----

From: kittycatangel_at_hotmail.com (Angel Cat) [mailto:kittycatangel_at_hotmail.com]
Posted At: Friday, January 31, 2003 2:41 PM Posted To: server
Conversation: tricky SQL SELECT - help!
Subject: Re: tricky SQL SELECT - help!

Hi all!

Please, I need SQL help not new db design!

This schema is created by a 3rd party tool that I have NO CONTROL over. I know the schema sucks but when you work with a tool that creates the schema for you, there's nothing you can do about it!

Please help! Received on Fri Jan 31 2003 - 09:46:22 CST