[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