left join

[hair]

hello People
Nice new house here huh

question: I have a query that show data from two tables and uses a third table for a condition. It's a left join so that shows the results even though in the second table I don't have records. Now, when after 'WHERE' I add a condition like: and X NOT IN(select X from thirdtable...), it isn't working anymore. I mean the left join, the query is working again as a inner join query. Do you guys have any idea? If is two abstract what I say I can post the query, but as a principle, in a left or right join query, are some hidden rules that have to be respected? (at least, hidden for me )

Thanks in advance

 

[dcx693]

Please post a simple version of your query so we can get a better idea of what you're talking about.

 

[hair]

Please post a simple version of your query so we can get a better idea of what you're talking about.

SELECT tblAgent.codeagent, tblAgent.nomag, tblDebitcredit.cumuldebit, tblDebitcredit.cumulcredit, tblAgent.heurecontrat
FROM (tblAgent INNER JOIN tblDatesAgent ON tblAgent.codeagent = tblDatesAgent.codeagent) LEFT JOIN tblDebitcredit ON tblAgent.codeagent = tblDebitcredit.codeagent
WHERE (((tblAgent.codeagent) Not In (Select tbldatesagent.codeagent from tbldatesagent where tbldatesagent.datesortie is not null and( (datesortie+2)<=now) )) AND ((tblDebitcredit.semaine)=0))
ORDER BY tblAgent.nomag;

If I take out the where and what is after is working. Might be the fact that in the NOT IN I call a third table actualy? I have to specify that in the tblDatesAgent there are always records, the problem is in the tblDebitCredit, there I have holes sometimes.

Thanks is advance