Thread: Re: Query, view join question.
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 witha "afgewerkt" columns > 0) as created above ( with WHERE abo_his.abonnement = 238) there could be a record with bothabonnement = 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
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
>
"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;
> 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;
Yes, but the join isn't testing abo_klt.abonnement. It's testing
abo_his.abonnement. If there's a reason to think that rows in the two
tables with the same klantnummer must also have the same abonnement,
you have not said what it is.
regards, tom lane
I think there is an echo in here :) It's probably me. I pass for the rest of this thread. > "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; > >> 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; > > Yes, but the join isn't testing abo_klt.abonnement. It's testing > abo_his.abonnement. If there's a reason to think that rows in the two > tables with the same klantnummer must also have the same abonnement, > you have not said what it is. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >