Re: Query, view join question.

I think I stated in my previous post but in order to make your view
consistent with your original query I think you should do:

CREATE OR REPLACE VIEW even AS
SELECT DISTINCT abo_his.klantnummer,
abo_his.artikelnummer, abo_his.code_retour,
abo_klt.aantal_abonnementen, abo_klt.afgewerkt
    FROM abo_his
    JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer
   WHERE abo_klt.abonnement = 238              // I CHANGED THIS LINE
   ORDER BY abo_his.klantnummer, abo_his.artikelnummer,
 abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt;

It should not be

   WHERE abo_his.abonnement = 238

Unless you expect abo_his.abonnement always equal to abo_klt.abonnement


> Hi Tom,
>
> pgsql-general-owner@postgresql.org schreef:
>> "Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> writes:
>>> CREATE OR REPLACE VIEW even AS
>>>  SELECT DISTINCT abo_his.klantnummer,
>> abo_his.artikelnummer, abo_his.code_retour,
>> abo_klt.aantal_abonnementen, abo_klt.afgewerkt
>>>    FROM abo_his
>>>    JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer
>>>   WHERE abo_his.abonnement = 238
>>>   ORDER BY abo_his.klantnummer, abo_his.artikelnummer,
>> abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt;
>>
>> Okay ... but the view is constraining abo_his.abonnement and
>> outputting abo_klt.aantal_abonnementen.  Why would you assume that
>> joining on klantnummer would cause these two fields to necessarily be
>> the same?
>
> In the table abo_klt there is no record where abo_klt.abonnement = 238 and
> abo_klt.afgewerkt > 0:
>
> munt=# select * from abo_klt where abonnement = 238 and afgewerkt > 0;
> ...
> (0 rows)
>
> So I assumed that in no join between abo_his (which has no "afgewerkt"
> column at all ) and abo_klt (which has 0 records with a "afgewerkt"
> columns > 0) as created above ( with WHERE abo_his.abonnement = 238) there
> could be a record with both abonnement = 238 and afgewerk >0.
>
> But there are:
>
> on the view there are :
> munt=# select * from even where  afgewerkt > 0;
> .....
> (797 rows)
>
> SO I must understand something wrong...
>
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>