I'm trying to figure out how to do looping in SQL. I'd like to end up with the results at the bottom:
Items Id ItemName 1 Phone 2 Table 3 Lamp 4 TV 5 Stereo
ItemsByRooms Id
Requires Free Membership to View
Room RoomItems 1 Bedroom Phone, Lamp, Bed, TV 2 Kitchen Phone, Table 3 LvRoom Phone, Lamp, TV, Stereo
Results would be:
Id ItemName QtyUsed 1 Phone 3 2 Table 1 3 Lamp 2 4 TV 2 5 Stereo 1
Any suggestions?
This design is the opposite, if you will, of presenting one-to-many relational data in a single row (see Consolidate data on multiple rows into one (01 August 2003). In your case, you are storing it that way.
You have broken the law of First Normal Form (check out the neat PowerPoint animation on that site). Your penalty is that simple, easy, efficient SQL will not be available to you. Sorry.
But hey, maybe you didn't design it. Many people working with databases have to cope with the status quo of unfortunate design decisions by some predecessor, or code that would break if the design were changed, or management restrictions on where to allocate redesign resources, or whatever. These people still need answers, and "If this is your design, change it" won't cut it with them.
So. To find the rooms that have a particular item, you can use LIKE:
select Room from ItemsByRoom where RoomItems LIKE '%Table%'
The problem with this, of course, is that the database will probably do a table scan, because an index on RoomItems will not be efficient.
As for getting the result set that you wanted, namely a count of how often each item occurs in all rooms, you would be best to do that in your code. Return the Items table by itself, save it into an array, return the ItemsByRooms table, and then perform string-handling functions on the RoomItems field, updating a counter in the matching array entry. This solution works, but very inefficiently. Also, be aware that you have actually added to the problem; there is now yet another piece of code that will break when the table is redesigned.
By the way, in Joe Celko's webcast Advanced SQL tricks: Thinking in sets, Joe shows how to use SQL to pull out all the terms in a comma-delimited list:
select I1.keycol
, cast( substring(','||instring||','
from S1.seq + 1
for S2.seq - S1.seq - 1) as integer )
from InputStrings as I1
, Sequence as S1
, Sequence as S2
where substring(','||instring||','
from S1.seq
for 1 ) = ','
and substring(','||instring||','
from S2.seq
for 1 ) = ','
and S1.seq < S2.seq
and S2.seq = ( select min(S3.seq)
from Sequence as S3
where S1.seq < S3.seq )
The seq column in the Sequence table contains integers from 1 through one more than the maximum number of terms allowed. In the case of your items in a room, you would not need to CAST as INTEGER because your terms are strings.
If the above query were declared as a view, let's say NormalizedRoomItems, then your query for the count of how often each item occurs in all rooms would be:
select Items.Id
, Itemname
, count(*) as QtyUsed
from Items
left outer
join NormalizedRoomItems
on Items.Id = NormalizedRoomItems.Id
And, of course, it should be obvious that this is exactly the simple, easy, efficient SQL that you would use if the table were designed in first normal form.
This was first published in August 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation