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 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
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.