From: | "Pascal Tufenkji" <ptufenkji(at)usj(dot)edu(dot)lb> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Is there a bug in PostgreSQL ? |
Date: | 2008-12-10 14:33:07 |
Message-ID: | 008701c95ad4$38870870$150fa8c0@interne.usj.edu.lb |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello,
I'm writing a query with a left join to a view, and the server is giving me
a wrong result.
SELECT emp_id,institution from sip_carriere where emp_id = 342 and
institution = 1;
emp_id | institution
--------+-------------
342 | 1
(1 row)
SELECT * from sip_demissionaire where emp_id = 342;
emp_id | demission_date
--------+----------------
(0 rows)
IF I COMBINE THE TWO QUERIES WITH A LEFT JOIN QUERY AND PUT ONLY ONE
CONDITION, IT WOKS JUST FINE :
SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join
sip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 342 ;
emp_id | institution | emp_id | demission_date
--------+-------------+--------+----------------
342 | 1 | |
342 | 63 | |
342 | 85 | |
(3 rows)
SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join
sip_demissionaire d on d.emp_id = c.emp_id where c.institution = 1;
emp_id | institution | emp_id | demission_date
--------+-------------+--------+----------------
342 | 1 | |
... | ... | ...| ...
BUT IF I PUT BOTH CONDITIONS
SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join
sip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 342 and
c.institution = 1;
emp_id | institution | emp_id | demission_date
--------+-------------+--------+----------------
(0 rows)
What's the problem ?
I'm sure that the problem is with the view "sip_demissionaire" cause when I
copied its content to a temp table, the query returned a result.
SELECT * into temp foo from sip_demissionaire ;
SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join foo
d on d.emp_id = c.emp_id where c.emp_id = 342 and c.institution = 1;
emp_id | institution | emp_id | demission_date
--------+-------------+--------+----------------
342 | 1 | |
(1 row)
Here's the description of the view "sip_demissionaire" in case you need it
CREATE VIEW sip_demissionaire AS
(
SELECT t1.* from
(
SELECT emp_id,max(demission_date) as demission_date
from sip_carriere_dates
where demission_date is not null
group by emp_id
) as t1
left join
(
select emp_id
from sip_carriere_dates
where demission_date is null
) as t2 on t1.emp_id = t2.emp_id
where t2.emp_id is null
);
I know it's a long mail, but I'd appreciate any help
Thx in advance
Pascal
From | Date | Subject | |
---|---|---|---|
Next Message | Filip Rembiałkowski | 2008-12-10 14:45:36 | Re: Collapsing (select) row values into single text field. |
Previous Message | Bruce Momjian | 2008-12-10 13:33:29 | Re: inconsistent automatic casting between psql and function |