NYCPHP Meetup

[Kristina Anderson]

//======
SELECT i.id, i.name, i.whatever,
COALESCE(i.specific1, d.default1) as val1,
COALESCE(i.specific2, d.default2) as val2
FROM tblInstance i
LEFT OUTER JOIN tblDefault d
ON i.foreignKey = d.primaryKey;

COALESCE selects the first non-null value of its arguments, and the 
left outer join makes sure all records from the left table are returned.
//====

Here is a nifty example of the COALESCE function that I found.  So 
basically this function is used to handle possible NULL values in a 
join ... or...?  

Is this a MySQL specific thing or do other DBs use this function?


--Kristina 




> Hi John:
> 
> On Tue, Apr 22, 2008 at 01:31:52PM -0400, John Campbell wrote:
> > 
> > I have a products table with a standard auto number primary key, 
and a
> > descriptions table that is keyed off the product id and a language 
id
> > ('en','es','zh_cn', etc)
> ...
> > SELECT product.id, product.price, (SELECT d.description FROM
> > descriptions d WHERE d.product_id=product.id AND d.lang_id IN
> > (:1,'en') ORDER BY d.lang_id!='en' DESC LIMIT 0,1) as description
> > FROM product
> > WHERE product.category=:2
> 
> Don't use sub selects unless really necessary.  They kill performance.
> 
> Also, it's very helpful to use the same column names for the same 
thing 
> throughout the database.  For example, use "product_id" in both the 
> product and descriptions tables.  Makes things clearer and allows use 
of 
> "USING".
> 
> Also also, use a consistent naming convention.  You've got plural 
> descriptions and singular product.
> 
> Personally, I dislike aliasing tables (unless you're using the same 
table 
> twice, of course) because it obfuscates the query.
> 
> Anyway, all that aside, except the first point, here's how I'd do it:
> 
> 
> SELECT product.id, product.price,
> COALESCE(user_lang.description, default_lang.description) AS 
description,
> COALESCE(user_lang.lang_id, default_lang.lang_id) AS lang_id
> 
> FROM product
> LEFT JOIN descriptions AS user_lang
>   ON (
>     user_lang.product_id = product.id
>     AND lang_id = :1
>   )
> LEFT JOIN descriptions AS default_lang
>   ON (
>     default_lang.product_id = product.id
>     AND lang_id = 'en'
>   )
> 
> WHERE product.category = :2
> 
> 
> --Dan
> 
> -- 
>  T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
>             data intensive web and database programming
>                 http://www.AnalysisAndSolutions.com/
>  4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
> 
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
> 
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php
> 
>