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
