[Vserver+V4RB] Querying a table JOINed to itself

[Eric Ferrer]

> Such descriptions do not help me.

Yes, that's true, I'm not that clear.

So

Table "Articles" :
    Field ID = "ART123"
    Field Name = "My Article"

Table "PriceLists"
This table contains BOTH Price Lists Name and actual article prices.
Some records are the price lists (= catalog), other records are the article
prices.
Here are 3 Price Lists (not the article prices) :
    Field ID = "PRC345"
    Field Name = "2003 Price List"
    Field ParentPriceListID = ""
    Field ParentArticleID = ""
    Field Price = 0
    Field Owners = "TV"
    -
    Field ID = "PRC456"
    Field Name = "2003 Price List"
    Field ParentPriceListID = ""
    Field ParentArticleID = ""
    Field Price = 0
    Field Owners = "PA" 'note that I won't be able to see this Price List
    -
    Field ID = "PRC567"
    Field Name = "2004 Price List"
    Field ParentPriceListID = ""
    Field ParentArticleID = ""
    Field Price = 0
    Field Owners = "TV PA"

So, to this point, I have one article and 3 price lists, but I still have no
prices stored for this article for all price lists:
SELECT
N.Name, P.Price
FROM
PriceLists N,
PriceLists P
WHERE
N.Owners LIKE 'TV'
AND
N.ParentPriceListID = ''
AND
N.ID *= P.ParentPriceListID
AND
P.ParentArticleID = 'ART123'

I Get :
2003 Price List          0
2004 Price List          0

... Which is normal, since I have no prices yet stored for this article.

Then I start storing prices for this article, so I add a record in the Table
PriceLists. I start with the 2003 prices:
    Field ID = "PRC678"
    Field Name = ""
    Field ParentPriceListID = " PRC345"
    Field ParentArticleID = " ART123"
    Field Price = 300
    Field Owners = "TV"

Performing the same SQL statement above, I still get:
2003 Price List          0
2004 Price List          0
... Though I need to get:
2003 Price List          300
2004 Price List          0

Now if I add a second record to store the 2004 price :
    Field ID = "PRC789"
    Field Name = ""
    Field ParentPriceListID = " PRC567"
    Field ParentArticleID = " ART123"
    Field Price = 310
    Field Owners = "TV"
Now I get:
2003 Price List          300
2004 Price List          310


SO, the problem is :
I can't see my prices until ALL prices are stored in the database.
I would like my users to see the prices they store step by step.
And they should be able not to stored some prices for too old price lists.

Maybe this problem comes from the fact that I should have created two
tables, one for the price lists, one for the article prices...

Thanks again for your support
Eric