SQL query
I am running the Postgres(8.2.11) on Windows.
I have 2 tables, one with users and one with locations.
user_table
---------------
user_id user_code price value
1 22222 45.23 -97.82
2 33333 42.67 -98.32
3 44444 35.56 -76.32
locations
--------------
id code price value
1 22222 45.23 -97.82
2 33333 42.67 -98.32
3 44444 43.26 -98.65
I have a query that takes every user and looks into locations to see if the
code, price and value match up. If they do then count it.
select count(*)
from user_table u, locations l
where u.user_code = l.code
and u.price = l.price
and u.value = l.value;
The answer to this should be 2, but when I run my query I get 4 (in fact
more entries than user_table) which is incorrect. What am I doing
incorrectly? I have been breaking my head over this for a while. Is there
some other query to get the required results? Any help would be highly
appreciated. I gave a simple example above, but the query runs over 2 tables
with about a million entries in each. So I am unable to verify what is
wrong, but I know the count is incorrect as I should not have more than what
is in the user_table.
Thanks,
Michael
On 22/11/2008 04:33, Michael Thorsen wrote:
select count(*)
from user_table u, locations l
where u.user_code = l.code
and u.price = l.price
and u.value = l.value;The answer to this should be 2, but when I run my query I get 4 (in fact
Are you sure that's the query that's being run? I just tried it here,
and got 2 - this was using your data above.
What do your table definitions look like? - here's what I did:
CREATE TABLE user_table
(
user_id integer NOT NULL,
user_code integer NOT NULL,
price numeric(6,2) NOT NULL,
"value" numeric(6,2) NOT NULL,
CONSTRAINT user_pk PRIMARY KEY (user_id)
)
WITH (OIDS=FALSE);
CREATE TABLE locations
(
id integer NOT NULL,
code integer NOT NULL,
price numeric(6,2) NOT NULL,
"value" numeric(6,2) NOT NULL,
CONSTRAINT location_pk PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
Does this correspond to what you have?
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
On 22/11/2008 16:07, Michael Thorsen wrote:
For the most part yes. The price and value were "real" columns,
otherwise the rest of it is the same. On a small data set I seem to get
That's almost certainly the problem, so - rounding errors are causing
the equality test in the join to fail. You should use NUMERIC for those
floating-point values.
Have a look at what the docs say on REAL and family:
http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYPE-FLOAT
Ray.
PS - please don't top-post, as it makes the thread difficult to follow.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
Import Notes
Reply to msg id not found: 4c8d33a90811220807h56da5c24w1f94d731ebebdb19@mail.gmail.com
"Michael Thorsen" <mthorsen1980@gmail.com> writes:
... I gave a simple example above, but the query runs over 2 tables
with about a million entries in each. So I am unable to verify what is
wrong, but I know the count is incorrect as I should not have more than what
is in the user_table.
You could easily get a count larger than the number of rows in
user_table, if there are rows in user_table that join to multiple rows
in the locations table. So look for duplicated data in locations ...
regards, tom lane