I have a view of a content type that shows several fields for each node. The view works fine if distinct is set to NO: I see the correct list of results.

If I add the distinct:YES setting, it returns no results. Here is the query. Am I missing something obvious?

SELECT DISTINCT(node.nid) AS nid,
node.title AS node_title,
node.changed AS node_changed,
node_data_field_job_onet_occupation.field_job_onet_occupation_nid AS node_data_field_job_onet_occupation_field_job_onet_occupation_nid,
node_data_field_job_onet_occupation.nid AS node_data_field_job_onet_occupation_nid,
node.type AS node_type
FROM node node
LEFT JOIN content_field_job_onet_occupation node_data_field_job_onet_occupation ON node.vid = node_data_field_job_onet_occupation.vid
WHERE (node.type in ('resume')) AND (node.status <> 0)
ORDER BY node_changed ASC

CommentFileSizeAuthor
#6 untitled.JPG143.64 KBmayur.pimple

Comments

toddwoof’s picture

toddwoof commented

Update: I found the problem. I had patched the search.module with the patch for partial word search on this page http://drupal.org/node/103548 #50. This evidently breaks the Distinct setting, and also causes duplicate search results when using an exposed filter in Views. Reverting to the unpatched search makes Distinct work properly.

cpelham’s picture

cpelham commented

I have never applied that patch but I still have the problem with distinct:YES displaying no nodes.

scotthenning’s picture

scotthenning commented

I too get a query error with no results when I have Distinct:Yes. I have never applied the search patch...

scotthenning’s picture

scotthenning commented

This solved my error: http://drupal.org/node/284392#comment-1049183

harry slaughter’s picture

harry slaughter
Location Omaha NE USA
commented
Status: Active » Closed (fixed)
mayur.pimple’s picture

mayur.pimple commented
StatusFileSize
new untitled.JPG143.64 KB

SELECT DISTINCT(node.nid) AS nid,
users.picture AS users_picture,
users.uid AS users_uid,
users.name AS users_name,
users.mail AS users_mail,
profile_values_profile_email.value AS profile_values_profile_email_value,
profile_values_profile_email.uid AS profile_values_profile_email_uid,
profile_values_profile_mobile.value AS profile_values_profile_mobile_value,
profile_values_profile_Address.value AS profile_values_profile_Address_value
FROM node node
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN profile_values profile_values_profile_email ON users.uid = profile_values_profile_email.uid AND profile_values_profile_email.fid = '5'
LEFT JOIN profile_values profile_values_profile_mobile ON users.uid = profile_values_profile_mobile.uid AND profile_values_profile_mobile.fid = '4'
LEFT JOIN profile_values profile_values_profile_Address ON users.uid = profile_values_profile_Address.uid AND profile_values_profile_Address.fid = '2'
GROUP BY nid

this is display same value in multipal times

mayur.pimple’s picture

mayur.pimple commented

how to remove duplicate value