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 5 1966
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.
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_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.
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!
"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_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.
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_descripti on | varchar(80)
|
| long_descriptio n | text
|
| picture_name | varchar(16)
|
| manufacturer_id | mediumint(5) unsigned
|
| distributor_id | mediumint(5) unsigned
Thanks.
Steve
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_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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Vic |
last post by:
Hi all,
When I test the Delete multi table function in MySQL,
DELETE table_name ...]
FROM table-references
I accidentally delete all data in one table. All data in that table
are gone when I try to select them out in Control Center. But when I
go into the /mysql/data/mydatabase/, I see a MYD, MYI, frm for that
table. And it seems that data is still inside the MYD, althought it's
|
by: David Logan |
last post by:
Hello,
I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the other tables.
PlanID is the primary in Plan and
Plan Provider ProviderLocation Lookups
-------- ---------- ---------------- -----------
PlanID ProviderID ProviderID LookupType
|
by: Joe |
last post by:
I have the following 3 tables:
Clients, which has a numeric PK field called CLIENT_ID
Languages, which has a numeric PK field called LANGUAGE_ID
Client_Languages, which has a unique PK and foreign key fields
CLIENT_ID and LANGUAGE_ID
Clients and Languages each have a one-to-many relationship with
|
by: Joe |
last post by:
I have 2 multi-list boxes, 1 displays course categories based on a
table called CATEGORIES. This table has 2 fields CATEGORY_ID,
CATEGORY_NAME
The other multi-list box displays courses based on a table called
COURSES. This table has 2 fields CATEGORY_ID, COURSE_NAME.
The CATEGORY_ID is a FK in COURSES and a PK in CATEGORIES.
I want to populate the course list box based on any category(s)
|
by: syounger |
last post by:
Hi. I have a report in Access 2000 that is based on selection made
from a series of interdependent list boxes. The boxes I have right now
are Source, Table, Column, Date. The user chooses Source first, then
the Table list box populates only tables from that source. Once a
table is chosen, only the columns for that table appear in the Column
list box. In the date box, the only dates that appear are those that
are stored against the...
| |
by: Tim Marshall |
last post by:
A2003. I am getting this error message when I try to set a report's
recordsource to an SQL statement or a saved querydef that uses sub-queries.
I've debug.printed the SQL, and run it as a stand alone query, as a
rowsource for a list box and as a recordsource for a form. There's no
problem. I'm only encountering this in reports.
As a stop gap, I'm going to use a form for a printe report, bvut I
wonder if anyone has encountered...
|
by: Andy_Khosravi |
last post by:
I'm trying to build a search utility for users to find 'inquiries' in
my database that involves several tables. This is normally easy to do
with the query builder, but I have a unique situation involving a multi
select listbox. Unfortunatly, my SQL skills are somewhat limited, so
I'm not sure if there is an easy way around it. To simplify the
explanation, I'll simplify the table/field setup to get at the meat of
the question.
I have a...
|
by: 663scott |
last post by:
Hi
I am pretty new to ACCESS. I have created some small databases previously. I need to run a simple query searching for a USERNAME which will gather information from five to ten tables containing seperate bits of information and combine them into a report. The primary key on all of the tables is the username.
AS AN EXAMPLE:
TABLE 1
USERNAME
DATE JOINED
|
by: KrazyKasper |
last post by:
Access 2003 – Multi-Column List Box – Select Multiple Items
I have a multi-column (3 columns) list box that works well to select one set of records or all sets of records (based on the first field in the list box). I need to it also select multiple sets of records (Multi-Select = Extended).
I modified my code based on code I found on some Internet site which gave an example using three fields in a three field table. It loops through the...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |