[thelist] sql/table design help

Anthony Baratta Anthony at Baratta.com
Wed Nov 20 22:48:01 CST 2002


At 08:03 PM 11/20/2002, Tom Dell'Aringa wrote:
>Yes, me again with more questions on sql and my table design. Here's
>the trouble: I have a table that has items. These items sometimes
>have more than one size, therefore more than one price. When I list
>them, I only want to list the item once, and then all the prices.

Here's my 2 cents. If rudy disagrees - listen to him. ;-)

         Table_One

         ItemID
         ItemName

         Table_Two
         FeatureID
         FeatureName
         ItemPrice
         ItemID

Then query the data like this:

         SELECT Table_One.ItemID
         , Table_One.ItemName
         , Table_Two.FeatureID
         , Table_Two.FeatureName
         , Table_Two.ItemPrice
         FROM Table_One
         JOIN Table_Two on Table_One.ItemID = Table_Two.ItemID
         ORDER BY Table_One.ItemID, Table_Two.ItemPrice

Then as you loop through your record set, you just ignore the repeats in
the ItemName.

pseudo code

         TrackingID = 0
         While NOT RS.EOF
                 if TrackingID <> TableID then
                         TrackingID = TableID
                         PrintOut strItemName
                 end if
                 PrintOut Feature & Price
         wend

/pseudo code


---
Anthony Baratta
President
Keyboard Jockeys

"Conformity is the refuge of the unimaginative."




More information about the thelist mailing list