Re: [GENERAL] Query, view join question.
Joost Kraaijeveld [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Query, view join question.
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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Query, view join question.
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 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 Than I do a select * from miracle where commonvaluecol = 123 and value2 0 This query returns many rows. (How) Is this possible? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Query, view join question.
Joost Kraaijeveld [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Query, view join question.
Hi Tom, [EMAIL PROTECTED] schreef: Joost Kraaijeveld [EMAIL PROTECTED] 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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Query, view join question.
Joost Kraaijeveld [EMAIL PROTECTED] 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
Re: [GENERAL] 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, [EMAIL PROTECTED] schreef: Joost Kraaijeveld [EMAIL PROTECTED] 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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Query, view join question.
I think there is an echo in here :) It's probably me. I pass for the rest of this thread. Joost Kraaijeveld [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Query, view join question.
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Query, view join question.
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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
