[Vserver+V4RB] Querying a table JOINed to itself

[Eric Ferrer]

>> SELECT
>> N.Name, P.Price
>> FROM
>> PriceLists N,
>> PriceLists P
>> WHERE
>> N.Owners LIKE 'TV'   ''this is specific to my app
>> AND
>> N.ParentPriceListID = ''      '' to get price list name
>> AND
>> N.ID *= P.ParentPriceListID
>> AND
>> P.ParentArticleID = '123ABC'
>> 
>> I'd need the cursor to display 0.00 if no record found for one of the price
>> lists, i.e :
>> 
>> SOME ARTICLE NAME HERE
>> 2005 price:                 0 (not yet stored)
>> 2004 price:             200
>> 2003 Price:             159
>> 
>> But I can't make this work.
>> Thanks in advance for your help.
> 
> So what is problem?
> 
> You want get 0, but what you really get ?

All prices must exist for all found PriceLists, otherwise I get only 0
(which mean not found, since other prices exists).

Another example:
If I create a new article in my Articles table, all prices are zeroed for
all found pricelists (I'm not displaying all PriceLists stored in the
database, this is why I have a Owners LIKE 'TV' in my statement).
When I start storing all prices for the displayed PriceLists, I get only
zero values until I store ALL prices... Then I have the good values...

Thanks for your help
Eric