> I have this query working : > select profil,count(og.name) > from ldap l > left join uidinoldgroups ug on l.uid=ug.uid left join oldgroups og on > og.id=ug.idgroupe and og.rne='0410030k' and og.type='g' > where l.profilgeneral='P' > and l.rne='0410030k' > group by l.profil > > But if I put : > and og.rne='0410030k' and og.type='g' > in the where part (what you suggested and what I did naturally), I get 0 > results which is quite confusing (because it's an outer join). > > Also, if I replace these with full outer joins, I still get 0 results. > > With my data, if I replace og.type='g' with og.type='m' I get MOST OF my > resultats back (those where the count() returns more than zero). I know this > is specific to my data but I really don't get the behaviour. > > Thanks for your help, > > JC > The query you wrote is equivalent to this: SELECT profil, count(og.name) FROM ldap l LEFT JOIN ... ug ON (l.uid = ug.uid) LEFT JOIN (SELECT * FROM ... WHERE rne = '...' AND type = '...') og ON (og.id = ug.idgroupe) WHERE l.rne = '' AND l.type='' As soon as you start putting the right-hand tables in the outer-most where clause you have to deal with the fact that the outer join can cause those columns to be null. If you compare those columns to a literal value then you are saying that you don't want that column to be NULL and by extension you really meant to use an inner join. If you had put: WHERE l.rne = '' AND l.type = '' AND (og.type = 'g' OR og.type IS NULL) That would give you the behavior such that if og.type is a known value AND it is not 'g' then the item should NOT be counted. If the value is unknown or 'g' then count it. As for a rule-of-thumb I would avoid non-relational conditions in the ON clause all-together and move them into a sub-query as I showed above. The WHERE clause is reserved for the left side of the join hierarchy by default. The ON clause is reserved for inter-relation comparisons. When cobbling together multiple JOINs I would also suggest making use of liberal newlines as well as parentheses. Lastly knowing the correct answer is not that common; just starting building up the query piece-by-piece and keep in mind that you have to deal with the NULLs introduced by the OUTER JOIN. How you deal with them is query specific and cannot be "rule-of-thumbed". Yes, this is all more verbose but you've now separated the relational filter and the joining into the designated areas making interpreting the query easier. Throw in usage of CTE/WITH and the important portion of the query can be made succinct by moving the sub-queries and filters to the top and out of the way. You can refactor, if necessary, when you are done. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
