pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJOIN query not working as expected
4+ messages / 2 participants
[nested] [flat]
* JOIN query not working as expected
@ 2005-12-06 14:08 Mario Splivalo <mario.splivalo@mobart.hr>
2005-12-06 14:58 ` Re: JOIN query not working as expected Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Mario Splivalo @ 2005-12-06 14:08 UTC (permalink / raw)
To: 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:
SELECT
services.id AS service_id,
(SELECT
COUNT(id)
FROM
messages
WHERE
(messages.service_id = services.id)
AND (messages.receiving_time >= '2005-10-01')
AND (messages.receiving_time < '2005-10-30')
) AS "count",
services.keyword
FROM
services
WHERE
(services.vpn_id = 1)
AND
(
(services.start_time IS NULL OR services.start_time <= '2005-10-30')
AND
(services.end_time IS NULL OR services.end_time >= '2005-10-01')
)
GROUP BY
services.id,
services.keyword
ORDER BY
services.keyword
[this query shows correctly, for service 'cocker', that '"count"' column
has value 0]
I transformed query to this:
SELECT
services.id AS service_id,
count(messages.id) as "count",
services.keyword
FROM
services
LEFT OUTER JOIN messages
ON services.id = messages.service_id
WHERE
services.vpn_id = 1
AND messages.receiving_time BETWEEN '2005-10-01' AND '2005-10-30'
GROUP BY
services.id,
services.keyword
ORDER BY
services.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."
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: JOIN query not working as expected
2005-12-06 14:08 JOIN query not working as expected Mario Splivalo <mario.splivalo@mobart.hr>
@ 2005-12-06 14:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-06 15:00 ` Re: JOIN query not working as expected Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2005-12-06 14:58 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> 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');
> (0 rows)
> Why is that?
Probably, you've got some NULLs in the messages.service_id column ...
try excluding those from the sub-select.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: JOIN query not working as expected
2005-12-06 14:08 JOIN query not working as expected Mario Splivalo <mario.splivalo@mobart.hr>
2005-12-06 14:58 ` Re: JOIN query not working as expected Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-06 15:00 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-12-06 15:02 ` Re: JOIN query not working as expected Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Mario Splivalo @ 2005-12-06 15:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Tue, 2005-12-06 at 09:58 -0500, Tom Lane wrote:
> Mario Splivalo <mario.splivalo@mobart.hr> writes:
> > 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');
> > (0 rows)
>
> > Why is that?
>
> Probably, you've got some NULLs in the messages.service_id column ...
> try excluding those from the sub-select.
Yes, I've discovered that later. Still, I'm confused with the 'biggies'
at the bottom of my initial mail.
I neglected to metion that I'm using postgresql 7.4.8. I discovered that
I can create a FK on a column wich allows NULL values, and I can even
insert rows with NULLs in FK column, although PK table where FK is
pointing does not allow nuls. Is that 'by design', or...?
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."
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: JOIN query not working as expected
2005-12-06 14:08 JOIN query not working as expected Mario Splivalo <mario.splivalo@mobart.hr>
2005-12-06 14:58 ` Re: JOIN query not working as expected Tom Lane <tgl@sss.pgh.pa.us>
2005-12-06 15:00 ` Re: JOIN query not working as expected Mario Splivalo <mario.splivalo@mobart.hr>
@ 2005-12-06 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-12-06 15:02 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> I can create a FK on a column wich allows NULL values, and I can even
> insert rows with NULLs in FK column, although PK table where FK is
> pointing does not allow nuls. Is that 'by design', or...?
It's per SQL spec. Add a NOT NULL constraint to the column if you do
not wish to allow NULLs.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-12-06 15:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-06 14:08 JOIN query not working as expected Mario Splivalo <mario.splivalo@mobart.hr>
2005-12-06 14:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-06 15:00 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-12-06 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox