Hello All,

I have a bit of a problem I can't seem to figure out. I have a My SQL database with three tables (Items, Item Catagory, and Item Sizes). If someone wants to see a item category of Pizza I need to have all Items that are linked to Pizza to show but I also need to have all Pizza sizes to show in columns. For example: If you click on the pizza catagory I need to to return results such as:

-------------------------------------
Item |Sm | Med | Lg | Super
-------------------------------------
Plain Pie |$5 | $7 | $9 | $11
Meatball Pie|$6 | $8 | $11| $13
and so on...

But when I use inner join I get multiple records for Sizes. I am using the following SQL command:

SELECT items.ID, items.Catagory1ID, items.Name as Item, items.Description as Description, item_sizes.Name as ItemSize, item_sizes.Description as ItemDescription, item_sizes.Default, item_sizes.Cost

FROM items

INNER JOIN item_sizes

ON items.Catagory1ID = item_sizes.itemID

WHERE items.Catagory1ID = 1 ORDER BY items.item

Now it will return a total of 8 records a record for each size and the same pizza type but there are only two Pizza types (Plain and Meatball) and 4 sizes for each pizza. How can I code this out so it will only show the Pizza found from Items and show the pizza sizes available from Item Sizes table?

Here all my code that I have tried:

RecordSet.Open "SELECT items.ID, items.Catagory1ID, items.Name as Item, items.Description as Description, item_sizes.Name as ItemSize, item_sizes.Description as ItemDescription, item_sizes.Default, item_sizes.Cost FROM items INNER JOIN item_sizes ON items.Catagory1ID = item_sizes.itemID WHERE items.Catagory1ID = 1 ORDER BY items.item"

<table width="100%" border="0" cellspacing="0" cellpadding="0"> Response.Write "<tr>" 'Create a row
    Response.Write "<td></td>" Leave this column blank.


'The following code will create a table and add columns per each item size titles records it finds in Item Sizes.
For I = 1 TO RecordSet.RecordCount / 2
Response.Write "<td>" & RecordSet("ItemSize") & "</td>" 'Create a column

RecordSet.MoveNext

Next

Response.Write "</tr>" 'Close the row
RecordSet.MoveFirst

'The following will the show the Pizza's found in Items tables in the first Column.
Do While NOT RecordSet.EOF = True

Response.Write "<tr>" 'Create the first row and show the Pizza name.
Response.Write "<td>" & RecordSet("Item") & "</td>"


Do While NOT RecordSet.EOF = True

'Show the cost in the next column right under the size title
Response.Write "<td>" & FormatCurrency(RecordSet("Cost"), 2) & "</td>" 'Create a column with prices for sizes
                                        RecordSet.MoveNext 'Move to next record
                                        Loop

    Response.Write "</tr>" 'Close the row

Loop

But this isn't working the way I would like. Becuase of the 8 records it loops 8 times and repeats the sizes on the top column titles and only shows one pizza type rather than two. Can someone please help me figure out how to manage multiple records with inner join and showing it like the example above. Thank you!

Frank