SQL query

Поиск
Список
Период
Сортировка
От Michael Thorsen
Тема SQL query
Дата
Msg-id 4c8d33a90811212033x5e88177eo7a026b10336c975b@mail.gmail.com
обсуждение исходный текст
Ответы Re: SQL query  (Raymond O'Donnell <rod@iol.ie>)
Re: SQL query  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Michal Szymanski
Дата:
Сообщение: PgAdminIII - RAISE WARNING prints in one line
Следующее
От: Chris McDonald
Дата:
Сообщение: Best way to import a plpythonu module