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_i d
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]

Re: Multi Table Query Help

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_activit es) 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("SE LECT prod_id,-------other fields------------ FROM
prodcut,vendor WHERE product.vendor_ id=vendor.vendo r_id");
i, hope it work. see u.

[Pedro Graca]

Re: Multi Table Query Help

Steve wrote:[color=blue]
> 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_i d
> 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.[/color]

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_descrip tion,
v1.name as manufacturer,
v2.name as distributor
FROM product p,
vendor v1,
vendor v2
WHERE p.manufacturer_ id=v1.id
AND p.distributor_i d=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]

Re: Multi Table Query Help

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]

Re: Multi Table Query Help


"Pedro Graca" <hexkid@dodgeit .com> wrote in message
news:slrncsde53 .eqs.hexkid@ID-203069.user.uni-berlin.de...[color=blue]
> Steve wrote:[color=green]
> > 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_i d
> > 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.[/color]
>
> Please post your tables definition (use the output of the DESCRIBE
> command).[/color]

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_descripti on | varchar(80)
|
| long_descriptio n | text
|
| picture_name | varchar(16)
|
| manufacturer_id | mediumint(5) unsigned
|
| distributor_id | mediumint(5) unsigned

Thanks.

Steve

[Pedro Graca]

Re: Multi Table Query Help

Steve Edwards wrote:[color=blue]
>
> "Pedro Graca" <hexkid@dodgeit .com> wrote in message
> news:slrncsde53 .eqs.hexkid@ID-203069.user.uni-berlin.de...[color=green]
>> Please post your tables definition (use the output of the DESCRIBE
>> command).[/color]
>
> mysql> describe vendor
> -> ;[/color]
<snip badly formatted table description>
[color=blue]
> mysql> describe product
> -> ;[/color]
<snip>



NEW VERSION OLD VERSION

SELECT p.product_name, -- p.name
p.short_descrip tion, --
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_i d=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!