Обсуждение: 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
------------------------------------------------------------------

"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