[thelist] mysql - null - returns results anyway

Phil Turmel pturmel-webdev at turmel.org
Mon Dec 21 14:38:22 CST 2009


Bob Meetin wrote:
> My Christmas Wish  - the answer.
> 
> - Either NULL or ISNULL is perhaps the clue but I don't remember the
> answer.  I need to be able to returns results even if the vendor such as
> the one with id 21 has no products to link this together.
> 
> ------------------------------------------------------------
> 
> select
> 
> p3_products.id as pid,
> p3_products.name as product_name,
> p3_vendors.name as vendor_name,
> p3_product_categories.name as category_name,
> p3_products.jos_content_id as product_jos_content_id,
> p3_vendors.jos_content_id as vendor_jos_content_id,
> p3_product_categories.jos_content_id as category_jos_content_id
> 
> from
> 
> p3_products, p3_vendors, p3_product_categories
> 
> where
> 
> p3_products.category_id = p3_product_categories.id
> and p3_products.vendor_id = p3_vendors.id
> and p3_vendors.id = '21'
> 
> order by vendor_name, product_name asc
> 
> ------------------------------------
> 
> Bob

Hi Bob,

You need an outer join...  I'd use LEFT like so (untested):

Select
  p3_products.id as pid,
  p3_products.name as product_name,
  p3_vendors.name as vendor_name,
  p3_product_categories.name as category_name,
  p3_products.jos_content_id as product_jos_content_id,
  p3_vendors.jos_content_id as vendor_jos_content_id,
  p3_product_categories.jos_content_id as category_jos_content_id
From
  (p3_vendors Left Join p3_products On p3_products.vendor_id = p3_vendors.id)
  Left Join p3_product_categories On p3_products.category_id = p3_product_categories.id
Where
  p3_vendors.id = '21'
Order By
  vendor_name, product_name asc;

Merry Christmas,

Phil
-- 
Need to contact me offlist? 
  Drop -webdev or you probably won't get through.


More information about the thelist mailing list