Multi Table Query Help

[Steve]

I'm trying to do a multi table query that displays all the info I need
in one array, and I'm having problems figuring out how to do it.

The two tables are product and vendor. A vendor can be a manufacturer,
distributor, or both. This is indicated via the manufacturer_YN and
distributor_YN fields in the vendor table, and vendor_id is an
auto_number field. In the product table, there is a manufacturer_id and
a distributor_id field, and these are linked to the vendor.vendor_id
field.

What I would like to do is for each product, display the name, short
description (both fields in product table), manufacturer, and
distributor. The problem I'm having is writing a query to get all this
info at once so it can be displayed. I have a feeling it can be done
with a subquery, but haven't worked with those yet. Any help would be
appreciated.

Thanks.

Steve

[badr]

hi steve,
i wanna to display your tables first
product table:
------------------------------------------------------------------------------------------------------------------
prod_id | prod_name | prod_price | manufacturer_id | distributor_id |
-----so.on.....
------------------------------------------------------------------------------------------------------------------
vendor table :
------------------------------------------------------------------------------------------------------------------
vendor_id | vendor_name | manufacturer_YN | distributor_YN |
-----so.on.....
------------------------------------------------------------------------------------------------------------------
if is it ok lets go
--------------------------------
actully it will take time to make query (not more long) but if you use
MySQL you cannot use subquery cuz it not supportted (may be in the
MySQL 4.1.7 or 4.1.8) but i can give you new idea

instead of using 2 fileds in vendor manufacturer_YN , distributor_YN
you can make them 1 field say (vendor_activites) by this way you will
get more normalization and it will be more easy to make your query ,
and then remove manufacturer_id, distributor_id , and make it
vendor_id, i hence that your query may be somthing like this

mysql_query("SELECT prod_id,-------other fields------------ FROM
prodcut,vendor WHERE product.vendor_id=vendor.vendor_id");
i, hope it work. see u.

[Pedro Graca]

Steve wrote:

The two tables are product and vendor. A vendor can be a manufacturer,
distributor, or both. This is indicated via the manufacturer_YN and
distributor_YN fields in the vendor table, and vendor_id is an
auto_number field. In the product table, there is a manufacturer_id and
a distributor_id field, and these are linked to the vendor.vendor_id
field.

What I would like to do is for each product, display the name, short
description (both fields in product table), manufacturer, and
distributor. The problem I'm having is writing a query to get all this
info at once so it can be displayed.

Please post your tables definition (use the output of the DESCRIBE
command).

Just guessing I'd think something like this works:

SELECT p.name,
p.short_description,
v1.name as manufacturer,
v2.name as distributor
FROM product p,
vendor v1,
vendor v2
WHERE p.manufacturer_id=v1.id
AND p.distributor_id=v2.id
AND v1.manufacturer_YN='yes'
AND v2.distributor_YN='yes'
I guess (I'm pretty sure) the JOIN syntax would be better, but I never
liked that syntax and can't write a JOIN query without a lot of editing
out errors before it works the way I want :-)

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!

[lig]

Sounds like your trying to do a join. To learn about them try going to
http://sqlcourse2.com/joins.html , or google for "SQL joins".
References:
http://dev.mysql.com/doc/mysql/en/JOIN.html

[Steve Edwards]

"Pedro Graca" <he****@dodgeit.com> wrote in message
news:sl*******************@ID-203069.user.uni-berlin.de...

Steve wrote:

The two tables are product and vendor. A vendor can be a manufacturer,
distributor, or both. This is indicated via the manufacturer_YN and
distributor_YN fields in the vendor table, and vendor_id is an
auto_number field. In the product table, there is a manufacturer_id and
a distributor_id field, and these are linked to the vendor.vendor_id
field.

What I would like to do is for each product, display the name, short
description (both fields in product table), manufacturer, and
distributor. The problem I'm having is writing a query to get all this
info at once so it can be displayed.

Please post your tables definition (use the output of the DESCRIBE
command).

mysql> describe vendor
-> ;
+-------------------+-----------------------------
---+
Field | Type
|
-----------------+---------------
----------+
vendor_id | mediumint(5) u
increment |
name | varchar(50)
|
address | varchar(30)
|
city | varchar(20)
|
state | char(2)
|
zip | mediumint(5)
|
phone | varchar(10)
|
fax | varchar(10)
|
email | varchar(50)
|
url | varchar(80)
|
contact_name | varchar(30)
|
acct_num | varchar(22)
|
distributor_YN | enum('Y','N')
|
manufacturer_YN | enum('Y','N')
mysql> describe product
-> ;
+-------------------+-----------------------------
---+
| Field | Type
ra |
+-------------------+-----------------------------
---+
| product_sku | bigint(9) unsigned zerofill
|
| subcategory2 | varchar(30)
|
| product_name | varchar(50)
|
| short_description | varchar(80)
|
| long_description | text
|
| picture_name | varchar(16)
|
| manufacturer_id | mediumint(5) unsigned
|
| distributor_id | mediumint(5) unsigned

Thanks.

Steve

[Pedro Graca]

Steve Edwards wrote:

"Pedro Graca" <he****@dodgeit.com> wrote in message
news:sl*******************@ID-203069.user.uni-berlin.de...

Please post your tables definition (use the output of the DESCRIBE
command).
mysql> describe vendor
-> ;

<snip badly formatted table description>
mysql> describe product
-> ;

<snip>

NEW VERSION OLD VERSION

SELECT p.product_name, -- p.name
p.short_description, --
v1.name as manufacturer, --
v2.name as distributor --
FROM product p, --
vendor v1, --
vendor v2 --
WHERE p.manufacturer_id=v1.vendor_id -- ...=v1.id
AND p.distributor_id=v2.vendor_id -- ...=v2.id
AND v1.manufacturer_YN='Y' -- ...='yes'
AND v2.distributor_YN='Y' -- ...='yes'

I didn't guess too far off the first time :-)
Not many changes between the two versions.
I think you should have managed by yourself.

To try it, you can copy it as is, including the -- comments.
Happy Coding :-)

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!