Query help... 1

[cmmourik]

Plz help,

I have the following problem. I have a field memory_type which contains the following value 10,20,30.

item memory_type
------------
1 10,20,30

These values refer to another table which contains the normal values like

id value
-------------
10 | compact flash
20 | USB
30 | whatever

I want to transform this to

item field
----------------
1 compact flash
1 USB
1 whatever

Does anybody have an idea how to do this using SQl or is it not possible?

Thnx,

Chris

 

[r937]

if you write a query for this, it will be an extremely complex and very slow query (it always requires a table scan)

instead, if you normalize your table design, so that you never have more than one value in a column, your queries will be trivially easy to write and always lightning fast (because then they can use indexes)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[cmmourik]

I would never design a database which contains more values in one field. But unfortunatly I do have no control on the source data I get.

Thnx for your reply I will try to solve this in another way, but if anybody has an idea....

Cheerz,

CHris

 

[r937]

an idea? sure, lotsa ideas

start here --

http://sqlteam.com/forums/topic.asp?TOPIC_ID=20675

;-)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Bastien]

just use 2 queries...the first obtains the list of values and the second use the IN clause to pull those values out of the other table

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[r937]

bastien, it's not that easy

what if you had to do it for multiple items?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[cmmourik]

I am using MySQL 4.1.9 which doesn't support stored procedures I could possibly do it using a stored procedure, but I need a stable database and not a beta version of 5.x. Actually I think it's not possible using pure SQL without the stored procedure... But bastien if you have an example , indeed like rudy said there's about 500 items in the table.

 

[r937]

oh, i can do it in plain sql, no worries

i just don't want to show how, in case it encourages anyone to actually try it

it's complex and slow as cold glue



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[cmmourik]

rudy why not share it? I just want to try this if it is really that slow I won't use it but we are running mysql on very fast machines and it would be helpfull if I could compare the alternatives to the raw SQL solution that you could suggest. The only solution I can think of right now is using python.

Could u plz show me an SQL solution?

Thnx,

Chris

 

[TonyGroves]

You could use something like:
[tt]
SELECT i.item,m.value
FROM
memories m JOIN items i
ON FIND_IN_SET(m.id,i.memory_type)
[/tt]
As you can see, it's really really complex.

 

[r937]

oh, man, i forgot i was in the mysql forum!!

star for you, tony

the sql is very simple in mysql

(the solution is still slow, though)

it's only complex in other databases



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)