CODEWITHSUNDEEP
mysqlsqldrupal
shankardevy
shankardevy

Reputation: 4370

mysql distinct query not working

I have this mysql query for Drupal 6. However it doesn't return distinct nid as it is meant to be. Can someone help identify the bug in my code?

    SELECT DISTINCT( n.nid), pg.group_nid, n.title, n.type, n.created, u.uid, u.name, tn.tid     FROM node n 
INNER JOIN users u on u.uid = n.uid 
LEFT JOIN og_primary_group pg ON pg.nid=n.nid 
LEFT JOIN term_node tn ON tn.vid=n.vid 
WHERE n.nid IN ( 
      SELECT DISTINCT (node.nid) 
      FROM node node 
      INNER JOIN og_ancestry og_ancestry ON node.nid=og_ancestry.nid 
      WHERE og_ancestry.group_nid = 134 ) 
        AND n.status<>0 
        AND n.type NOT IN ('issue') 
        AND tn.tid IN (
             SELECT tid FROM term_data WHERE vid=199 AND ( LOWER(name)=LOWER('Announcement') OR LOWER(name)=LOWER('Report') OR LOWER(name)=LOWER('Newsletter') 
     )) ORDER BY n.created DESC

The only way I can get distinct nid is adding a groupby clause but that breaks my Drupal pager query.

Upvotes: 0

Views: 562

Answers (3)

Adriaan Stander
Adriaan Stander

Reputation: 166346

DISTINCT is meant to return the DISTINCT row selected, so not a single column as part of the select clause, but the ENTIRE select clause.

SELECT Syntax

The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both options. DISTINCTROW is a synonym for DISTINCT.

Upvotes: 1

Bigxiang
Bigxiang

Reputation: 6692

In Mysql , the result with distinct is determined by all selected columns, not by a distincted column, maybe you should write another query to get distinct nid.

Upvotes: 0

Raghu Chandra
Raghu Chandra

Reputation: 1132

Remove "Distinct" from first line of your query because already you have distinct id's in where clause. Then it should work.

Upvotes: 1

Related Questions