JOIN query not working as expected - Mailing list pgsql-sql
From | Mario Splivalo |
---|---|
Subject | JOIN query not working as expected |
Date | |
Msg-id | 1133878084.8161.22.camel@ekim Whole thread Raw |
Responses |
Re: JOIN query not working as expected
|
List | pgsql-sql |
How is this possible? I have two tables. 'services', and 'messages'. Each message can be assigned to one service, or it can be unnasigned. Therefore 'service_id' column in table 'messages' is not foreign-keyed to 'id' column in services table. services.id is PK for services, messages.id is PK for messages. Now, here goes: pulitzer2=# select * from services where id = 1001; id | keyword | type_id | vpn_id | start_time | end_time | day_boundary | week_boundary | month_boundary | recurrence | random_message_count ------+---------+---------+--------+------------+------------------------+--------------+---------------+----------------+------------+----------------------1001 |cocker | 1 | 1 | | 2005-10-20 12:00:00+02 | | | | 1 | (1 row) Ok, I have a service with id 1001 which is called 'cocker'. Now, I want all the messages for that service within certain period: pulitzer2=# select * from messages where service_id = 1001 and receiving_time between '2005-10-01' and '2005-10-30';id | from | to | receiving_time | raw_text | keyword | destination_id| vpn_id | service_id | status | reply ----+------+----+----------------+----------+---------+----------------+--------+------------+--------+------- (0 rows) Ok, no such messages. Now I want all services which didn't have any messages within certain period: pulitzer2=# select * from services where id not in (select distinct service_id from messages where receiving_time between '2005-10-01' and '2005-10-30');id | keyword | type_id | vpn_id | start_time | end_time | day_boundary | week_boundary | month_boundary | recurrence | random_message_count ----+---------+---------+--------+------------+----------+--------------+---------------+----------------+------------+---------------------- (0 rows) Why is that? I 'discovered' above mentioned when I was transforming this query: SELECTservices.id AS service_id,(SELECT COUNT(id)FROM messagesWHERE (messages.service_id = services.id) AND(messages.receiving_time >= '2005-10-01') AND (messages.receiving_time < '2005-10-30')) AS "count", services.keyword FROMservices WHERE(services.vpn_id = 1) AND( (services.start_time IS NULL OR services.start_time <= '2005-10-30') AND (services.end_timeIS NULL OR services.end_time >= '2005-10-01')) GROUP BYservices.id,services.keyword ORDER BYservices.keyword [this query shows correctly, for service 'cocker', that '"count"' column has value 0] I transformed query to this: SELECTservices.id AS service_id,count(messages.id) as "count", services.keyword FROMservicesLEFT OUTER JOIN messages ON services.id = messages.service_id WHEREservices.vpn_id = 1AND messages.receiving_time BETWEEN '2005-10-01' AND '2005-10-30' GROUP BYservices.id,services.keyword ORDER BYservices.keyword This query runs MUCH faster, but it omits the 'cocker' column, as if I used INNER JOIN. Any clues? I'm stuck here... Mike -- Mario Splivalo Mob-Art mario.splivalo@mobart.hr "I can do it quick, I can do it cheap, I can do it well. Pick any two."