0

I am having a MySQL query created by Drupal Views - it is returning duplicate values. As far as I can tell, this is because there is a one to many relationship between the nodes table and the registrations, and whereas the selection of the node should go without a hitch, the join is making there be duplicates.

Does this appear like it could be correct, based on the query here? This is more of an attempted understanding of the query, and what to do to fix it (which will unfortunately probably be inside the views interface, which while normally good, is not giving me the desired behavior currently)

Here's the query:

SELECT DISTINCT node.title AS node_title, node.nid AS nid, registration_node.registration_id AS registration_node_registration_id, node.created AS node_created
FROM 
{node} node
LEFT JOIN {registration} registration_node ON node.nid = registration_node.entity_id AND registration_node.entity_type = 'node'
WHERE (( (node.type IN  ('event')) AND (node.status = '1') ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Steven Matthews
  • 9,705
  • 45
  • 126
  • 232
  • Yes. It seems like a reasonable assessment. – Strawberry Aug 08 '13 at 17:25
  • Hrm, any idea of how to fix it? Preference would be for in views/Drupal, but I am open to straight up SQL changes too, because that'll give me something of a path to work on to fix it. – Steven Matthews Aug 08 '13 at 17:26
  • In each case, which result do you want. (It may be instructive to provide a sqlfiddle TOGETHER WITH THE DESIRED RESULT SET) – Strawberry Aug 08 '13 at 17:28
  • I want the same result in either case - I want to have distinct nodes ONLY - there can be multiple registrations per node, but I only care for one, the first, to be displayed. – Steven Matthews Aug 08 '13 at 17:30
  • In fact, there must be multiple registrations existing, but only one displayed. – Steven Matthews Aug 08 '13 at 17:30

0 Answers0