So I found the issue, it seems to be from products that we've copied variants. If I pull one of them up, the variants page looks like this http://i.imgur.com/PEyrs.png . However, if you click on any of those variants, there's nothing in the group dropdown. If I go into that product and create the group, then re assign every single one, then the product works. This seems like a bug to me, because copying them is what breaks it. Any ideas on a fix? Going through all the products and updating every single variant would take a really, really long time.
*edit
I'll note that I'm open to doing something via sql to get things inserted in the correct tables. I can provide a backup of my website if you guys need it to see the issue.
*second edit
I've tried doing something on my own, but I don't understand what should be happening within your table structure. It seems to me like the variant group ids in the smith_ProductVariant and smith_ProductVariantGroup tables should match, but mine don't. I was going to write a query updating the smith_ProductVariant table to match the id for the Smith_ProductVariantGroups table, but the smith_ProductVariantGroups table has a column for productid. To me that says that I'm not thinking about how these tables a structured correctly, because in my mind, there's no need for a productid column in the ProductVariantGroups table, you could reference the ProductId from the ProductVariant table. Here's an Excel file of the results of the following query for reference
http://dl.dropbox.com/u/4979877/variants.xlsx
select * from Smith_ProductVariant as pv INNER JOIN Smith_ProductVariantGroup as pvg on pv.VariantGroup = pvg.VariantGroup
*third edit
upon further investigation, I was able to fix it with this query
SET IDENTITY_INSERT Smith_ProductVariantGroup ON
GO
INSERT INTO Smith_ProductVariantGroup (VariantGroupID, ProductID, VariantGroup, VariantHelpURL, PortalId, Hide)
Select VariantGroupID, ProductID, VariantGroup, '', null, '0' from Smith_ProductVariant where VariantGroupID not in (select VariantGroupID from Smith_ProductVariantGroup)
GO
SET IDENTITY_INSERT Smith_ProductVariantGroup OFF
GO
However, could we get this bug fixed, so that copying the items works?