Here's my setup. I have three tables I am working with: product,
attribute, and vendor. An attribute is a variation of a product. For
instance, if I have a widget, a red widget would be one attribute, a green
widget would be a second, a blue one would be a third, and so on. A product
is identified by a nine digit SKU, and an attribute is identified by an 11
digit SKU. The attribute SKU is the product SKU plus a two digit
identifier. for instance, if the product SKU is 123456789, the first
attribute would be 12345678901, the second would be 12345678902, and so on.
The vendor table contains all vendors, and they can be either a manufacturer
or a distributor. Each vendor is identified by a vendor_id value. Each
product has a manufacturer and a distributor, idientified by the
product.manufacturer_id and product.distributor_id fields, where those two
fields contain a vendor_id.
I need to generate a report that lists all attributes for a given vendor
(the vendor is chosen from a drop down list in an HTML form). I am trying
to write a query that will get this data instead of having to do multiple
queries. I was going to use a subquery, but the version of MySQL on the
ISP's server is 4.0.22, so subqueries aren't supported (only after 4.1
according to the MySQL site).
So it looks like I need to go through two steps to get the data I need:
1) Get all records from the product table that have a certain
manufacturer_id value
2) For each product, get all corresponding rows in the attribute table.
With 4.0.22, is it possible to get this data in one query? Or do I need to
do something like:
1) Run first query to get list of products with appropriate manufacturer_id
2) Put each value in an array
3) For each product_sku, get the corresponding rows from the attribute table
Or is there a third option I should consider?
Thanks.
Steve
