Subquery Help

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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?



Re: Subquery Help

Quoted text here. Click to load it

Could you give us an example of that query then?

Quoted text here. Click to load it

Read a SQL manual, and pay special attention to the JOIN clause.

Met vriendelijke groeten,
Tim Van Wassenhove <

Re: Subquery Help

Steve Edwards wrote:

Quoted text here. Click to load it
Quoted text here. Click to load it

Normalize your data - the composite key in the attribute relation breaks
first normal form.


Site Timeline