By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,408 Members | 1,624 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,408 IT Pros & Developers. It's quick & easy.

Subquery Help

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On 2005-05-03, Steve Edwards <ra***********@hotmail.com> wrote:
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).
Could you give us an example of that query then?
Or is there a third option I should consider?


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

--
Met vriendelijke groeten,
Tim Van Wassenhove <http://www.timvw.info>
Jul 17 '05 #2

P: n/a
Steve Edwards wrote:
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.
<snip> Or is there a third option I should consider?


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

C.
Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.