Pushing the limits of a SQL view

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Pushing the limits of a SQL view

Post by ReverendDexter »

I'm working with a Miva database backend, and I'm attempting to create an export to the flat file format for Amazon. For those not familiar, Miva stores product data in two places. The first is as straight-up normal field values in a product table (s01_Products). The second is a little more involved: to keep extensiblity, Miva gives you the ability to define extra fields via a table s01_CFM_ProdFields,
with the values of those fields defined in s01_CFM_ProdValues.

s01_Products is much like you'd expect, typical product table with a uniqe id and some universal product details (name, description, price, cost, etc). I'll give examples of the other two tables so that you can get a better idea of how the extra fields work:

Example of s01_CFM_ProdFields:

Code: Select all

id    code   	      name
1 	MFCD 	    Manufacturer Code
2 	MFNM 	    Brand
3 	MFSKU       SKU
4 	MFUPC 	   UPC
5 	COLOR 	   Color
Example of s01_CFM_ProdValues:

Code: Select all

field_id   	 product_id   	 value
2                 8145           Acme
5                 8145           Blue
2                 8197           TimCo
s01_CFM_ProdValues.product_id is the foreign key link to s01_Products.id, so1_ProdValues.field_id is the foriegn key link to s01_CFM_ProdFields.id

Now, for the fun part. What I'd really like is a view that has all of the extra fields joined onto the s01_Products table as if they were normal everyday columns. I don't even know if this is possible. Basically, it's defining each extra column as if it were...

Code: Select all

SELECT `value` 
FROM `s01_CFM_ProdValues` 
WHERE `field_id` = 8 
AND `product_id` = this_row_product_id
Does anyone have any clues how I might go about this? I know that I could just go through the list of product ids and run the twenty or so queries to fill in the different extra fields for each product, for all however many thousand products, but that's a lot of queries to be sending back and forth (at least 21,000 just to do the export once!)

Any help would be greatly, greatly appreciated
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

Thinking about this over the weekend, I was being less than intelligent about my secondary approach. I can get away with doing two queries per item, not the 10 or 20 I was thinking about. I'd still like to know if it's even possible to do what I was asking, but I think the urgency of the matter has subsided.
Post Reply