Manage Learn to apply best practices and optimize your operations.

How to do looping in SQL

I'm trying to figure out how to do looping in SQL. I'd like to end up with the results at the bottom:

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

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.

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.