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(at)postgresql(dot)org schreef:
>> "Joost Kraaijeveld" <J(dot)Kraaijeveld(at)Askesis(dot)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(dot)Kraaijeveld(at)Askesis(dot)nl
> web: www.askesis.nl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>