Re: SQL query

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
------------------------------------------------------------------