Postgres Pro
Downloads
Home   >   mailing lists

Thread: Views

Views

From
Bob Pawley
Date:
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 ;

Re: Views

From
Michael Fuhr
Date:
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

Re: Views

From
Bob Pawley
Date:
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


> 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

Re: Views

From
Michael Fuhr
Date:
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

Re: Views

From
Bob Pawley
Date:
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


> 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