Parsing Records

[mainmast]

Hello all,

I have two tables in my database, one with information items and one with details about users. I have it setup like this:

Table1:

ID      Name
1       ItemName
2       ItemName2

Table2:

UserName        Items
Bob             1(4), 2(8)

The value in the Items field means that he has four of the Item with the ID of 1 in Table1 and eight of the Item with the ID of 2 in Table1. What I am trying to do is parse this field so it outputs the actual item name and then move on to the next item.

So it would be like this:

Bob has:
ItemName
ItemName
ItemName
ItemName
ItemName2
ItemName2
ItemName2
ItemName2
ItemName2
ItemName2
ItemName2
ItemName2


I hope I explained it well, please let me know if your confused! Thanks!!

 

[DotNetGnat]

BAD DATABASE DESIGN.

here is how we modify it...

UserName        Items  quantity
Bob             1         4
Bob             2         8

let me know if you cannot make this change to your db...then we can come up with the solution for your present situation...

-DNG

 

[mainmast]

I thought about doing it that way, but wouldn't that be worse? It would have to search through the entire table for what items Bob has then go to the other table to get the details on that item. Maybe I'm wrong?

 

[DotNetGnat]

SELECT UserName, Items, Quantity
FROM MYTABLE
GROUP BY UserName, Items

wont that be easy...

-DNG

 

[DotNetGnat]

or simply....

SELECT t1.UserName, t2.ItemName, t1.Quantity
FROM table1 t1, table2 t2
WHERE t1.item=t2.id
GROUP BY t1.UserName, t2.ItemName

-DNG

 

[Tarwn]

Actually, Iu would suggest a further modification to the User table design. Get rid of the Quantity field.
Right now if you add any more items to the user it will require 2 statements, and INSERT to add the item and an UPDATE to increase the quantity.
Same with a DELETE form the items table.

Instead, if you need the Quantity, do something like this:

SELECT Table1.UserName, Count(Table2.Name) as Quantity
FROM Table1 INNER JOIN Table2 ON Table1.item = Table2.id
GROUP BY Table1.UserName

And I would also suggest using an INNER JOIN when you plan to pull out the individual items, as an INNER JOIN is more efficient than comma listing the tables in your FROM.

-T