Re: Multi Table Query Help

From: Pedro Graca (hexkid_at_dodgeit.com)
Date: 12/20/04 

Date: 20 Dec 2004 12:17:02 GMT

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!

Relevant Pages

  • Re: Help with OO Model
    ... So in the example code where I show passing in a string: xxx.GetCompany ("Distributor"), you can pass in the actual class type ) and not have to deal w/ maintaining strings to represent the various company classes. ... I have a class Company, a class Publisher, a class Manufacturer and a class ... A Publisher is a Company ...
    (microsoft.public.dotnet.languages.csharp)
  • Domain Model/Object Design question
    ... finding the correct design pattern. ... A class "Publisher", which is a type of company with a particular ... "Manufacturer" as well, since they create books, but they do more than ... A company can be a manufacturer, publisher or distributor, or any ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Help with OO Model
    ... A Publisher is a Company ... A Manufacturer is a Company ... A Distributor is a Company ... public class Distributor: Role ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Domain Model/Object Design question
    ... Manufacturer, Publisher, Distributor which each have information ... "Manufacturer" as well, since they create books, but they do more than ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Win 2K OEM License, no cd
    ... But in regards to pre-installed OEM software, the "vendor" and the "manufacturer" are one and the same. ... But if you go to a RadioShack store and buy an HP machine there, then RadioShack is now the one responsible for making sure that you get the COA and the CD when you buy the computer. ... Eventually the vendor sent me the COAs and told me to get the CDs from MicroSoft. ...
    (microsoft.public.win2000.general)
Loading