Views
I have a table, Devices, with various values under the column Type.
I wish to view all of the information regarding two of these values 'monitor' and 'valve'.
The following gives me the information, but the rows are duplicated from one to three times. I would like to see one row for each of the values. I have attempted a number of variations but they all give me similar duplication.
Is this duplication due to the action of postgre as to the information I've entered into the tables , or is it due to the structure of the command?
If it is due to the command perhaps you can suggest a remedy.
Bob
------
create view p_id.trial
(P_ID_ID, Pipe_ID, Equipment_ID, Fluid_ID, Fluid, Type_, Function_, Device_ID, Device_Number, Association)
as select devices.p_id_id, pipe_id, equipment_id, process.fluid_id, process.fluid, devices.type_, devices.function_, device_id, device_number, devices.association
from p_id.devices, p_id.process
where devices.type_ = 'monitor'
or devices.type_ = 'valve'
and devices.fluid_id = process.fluid_id ;
On Sat, Dec 17, 2005 at 03:39:02PM -0800, Bob Pawley wrote:
The following gives me the information, but the rows are duplicated
from one to three times. I would like to see one row for each of the
values. I have attempted a number of variations but they all give me
similar duplication.Is this duplication due to the action of postgre as to the information
I've entered into the tables , or is it due to the structure of the
command?
Have you tried using parentheses to group the OR expressions in the
WHERE clause?
where (devices.type_ = 'monitor' or devices.type_ = 'valve')
and devices.fluid_id = process.fluid_id;
--
Michael Fuhr
Michael
That worked fine.
Thanks for the help. Could you point me to some documentation on the use of
parentheses? Perhaps something that describes what action the parentheses
accomplishes?
Bob
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgre General" <pgsql-general@postgresql.org>
Sent: Saturday, December 17, 2005 4:09 PM
Subject: Re: [GENERAL] Views
Show quoted text
On Sat, Dec 17, 2005 at 03:39:02PM -0800, Bob Pawley wrote:
The following gives me the information, but the rows are duplicated
from one to three times. I would like to see one row for each of the
values. I have attempted a number of variations but they all give me
similar duplication.Is this duplication due to the action of postgre as to the information
I've entered into the tables , or is it due to the structure of the
command?Have you tried using parentheses to group the OR expressions in the
WHERE clause?where (devices.type_ = 'monitor' or devices.type_ = 'valve')
and devices.fluid_id = process.fluid_id;--
Michael Fuhr---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
On Sun, Dec 18, 2005 at 02:52:23PM -0800, Bob Pawley wrote:
Thanks for the help. Could you point me to some documentation on the use of
parentheses? Perhaps something that describes what action the parentheses
accomplishes?
See "Lexical Precedence" and "Value Expressions" in the "SQL Syntax"
chapter of the documentation:
http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-PRECEDENCE
http://www.postgresql.org/docs/8.1/interactive/sql-expressions.html
--
Michael Fuhr
Thanks Michael
Bob
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgre General" <pgsql-general@postgresql.org>
Sent: Sunday, December 18, 2005 4:49 PM
Subject: Re: [GENERAL] Views
Show quoted text
On Sun, Dec 18, 2005 at 02:52:23PM -0800, Bob Pawley wrote:
Thanks for the help. Could you point me to some documentation on the use
of
parentheses? Perhaps something that describes what action the parentheses
accomplishes?See "Lexical Precedence" and "Value Expressions" in the "SQL Syntax"
chapter of the documentation:http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-PRECEDENCE
http://www.postgresql.org/docs/8.1/interactive/sql-expressions.html--
Michael Fuhr---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings