Query, view join question.

"Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> writes:

Hi all,
I have 2 tables:

table1 with the columns objectid, refobjectid, commonvaluecol and value1.
table2 with the columns objectid, refobjectid, commonvaluecol and value2.

A "select * from table2 where commonvaluecol = 123 and value2 > 0" returns no rows.

I create a view:

create view miracle as
select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2
from table1 joing table2 on table1.refobjectid = table2.refobjectid
where commonvaluecol = 123

regression=# create table table1(objectid int, refobjectid int, commonvaluecol int, value1 int);
CREATE TABLE
regression=# create table table2(objectid int, refobjectid int, commonvaluecol int, value2 int);
CREATE TABLE
regression=# create view miracle as
regression-# select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2
regression-# from table1 joing table2 on table1.refobjectid = table2.refobjectid
regression-# where commonvaluecol = 123;
ERROR: syntax error at or near "table2" at character 135
LINE 3: from table1 joing table2 on table1.refobjectid = table2.refo...
^
regression=# create view miracle as
regression-# select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2
regression-# from table1 join table2 on table1.refobjectid = table2.refobjectid
regression-# where commonvaluecol = 123;
ERROR: column table1.commonvalue does not exist
regression=# create view miracle as
regression-# select table1.objectid, table1.value1, table1.commonvaluecol, table1.refobjectid, table2.value2
regression-# from table1 join table2 on table1.refobjectid = table2.refobjectid
regression-# where commonvaluecol = 123;
ERROR: column reference "commonvaluecol" is ambiguous
regression=#

Please don't waste our time with erroneous examples.

regards, tom lane

Hi Tom,

I could give you access to the database itself if needed. But these are the actual tables and view. I hope I will never make any tpo's again to upset you this way.

CREATE TABLE abo_his
(
klantnummer int4,
abonnement int2,
artikelnummer int4,
omschrijving char(40),
nummer_vd_levering int2,
artikelnummer_gratis int4,
artikelnummer_gratis_2 int4,
artikelnummer_gratis_3 int4,
omschrijving_gratis_artikel char(40),
omschrijving_gratis_artikel_2 char(40),
omschrijving_gratis_artikel_3 char(40),
datum_selectie date,
ordernummer int4,
code_retour int2,
briefnummer int2,
orderbedrag_guldens numeric(8,2),
orderbedrag_valuta numeric(8,2),
aantal_besteld int4,
verzendkosten numeric(8,2),
handmatige_toevoeging int2
)
WITH OIDS;

CREATE TABLE abo_klt
(
klantnummer int4 NOT NULL,
abonnement int2 NOT NULL,
waardering_klant char(10),
gem_betaaltermijn int4,
reden_blokkade_oud char(40),
aantal_abonnementen int2,
herkomst int4,
datum_abonnee date,
datum_laatste_selectie date,
reden_blokkade int2,
datum_blokkade date,
max_bedrag_lev_jaar numeric(8,2),
bestelfrequentie_in_dagen int2,
incasso int2,
instap_categorie int2,
afgewerkt int2,
eenmaligemachtigingeerstekeer int2,
naar_ander_abo int2
)
WITH OIDS;

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;

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

You haven't given the complete information but the following is just a guess.

It seems that abonnement is in both tables.

The view is stating

abo_his.abonnement = 238

I assume that your initial query was

WHERE abo_klt.abonnement = 238 and
AND abo_klt.afgewerkt > 2

My guess is that you are asking the view a different question:

WHERE abo_his.abnnement = 238
AND abo_klt.afgewerkt > 2

"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?

regards, tom lane

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

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

"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.

On Thu, 2005-01-06 at 17:57 +0100, Joost Kraaijeveld wrote:

Hi Tom,

I could give you access to the database itself if needed. But these are the actual tables and view.

I hope I will never make any tpo's again to upset you this way.

no-one was upset. the point is just that you are more likely to get
useful answers when those who would help you do not first
have to guess what you did.

a simplified case, as you tried to show us, is excellent, but
you should test it first, and post a cut-and-paste copy of your
commands and output to minimize typos.

gnari

unsubscribe

Hi both,

Thanks for taking the trouble to help me. Based on your responses I realized that a multi key join should do what I wanted and it does.

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