Search Postgresql Archives

distinct not working in a multiple join

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi there,

I have some issues with the query attached at the end of this email.
If I run that query I got this output ( I have removed some of the 
fields) despite the distinct clause:

QC Q&A  | www.xxx.com | 44281
QC Q&A  | www.xxx.com | 44281
WhyMAX? |             | 44285

But, and here is the weird thing, if I remove g.group_id from the
selection list then I got what I expected:

QC Q&A  | www.xxx.com | 44281
WhyMAX? |             | 44285
toto    | rufus       | 44286

Can someone explain me what is going on here? What am I missing?

Thanks for you help in advance,

David


            SELECT          
                distinct ar.title,
                ar.raw_data,
                ar.upload_dt,
                ar.artifact_id,
                g.group_id,
                acl.read
            FROM
                artifact_acl acl,
                artifacts a,
                artifact_revisions ar,
                revisions_to_types rt,
                artifact_types at,
                groups g
            WHERE
                a.expire_dt > NOW() and
                acl.artifact_id = a.artifact_id and
                a.published_revision = ar.revision_id and
                ar.revision_id = rt.revision_id and
                rt.type_id = at.type_id and
                acl.group_id = g.group_id and
                a.suppress = false and
                at.is_resource = true and
                (
                acl.group_id = (
                                select
                                    group_id
                                from
                                    groups
                                where
                                    group_nm = 'ting'
                               )
                               OR
                acl.group_id = (
                                select
                                    group_id
                                from
                                    groups
                                where
                                    group_nm = 'tors'
                               )
                ) and
                acl.group_id IN (4,17,54,2,1,123) and
                acl.read = true and
                ((g.back_or_front = 'front') or
                 (g.group_nm = 'PR Admin'))
            GROUP BY
               ar.title
            ORDER BY
               ar.upload_dt DESC
            LIMIT 3;


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux