Using distinct on more than one field

[Rob Stocker]

Hi Guys,
I'm stuck on some sql logic ,

I'm trying to left join two tables. Normally left joining increases the amount of rows if there are duplicates on the right table. However I need to ignore the duplicates, so I've used a distinct sub query. I'm sure this is the wrong technique but I've made more progress this way although I'm still not there.

the problem is I need to access two fields in this subquery but only one is to be distinct:


a) works but no access to civicrm_contribution.receive_date

$db->setQuery('SELECT civicrm_membership.membership_type_id FROM civicrm_membership LEFT JOIN (SELECT DISTINCT contact_id FROM civicrm_contribution) as a ON civicrm_membership.contact_id = a.contact_id WHERE civicrm_membership.end_date = "2011-12-31" ');


This would be ideal but doesn't work because the receive_date is being included in the DISTINCT .

b) doesn't work because receive date is included in the Distinct bit

$db->setQuery('SELECT civicrm_membership.membership_type_id FROM civicrm_membership LEFT JOIN (SELECT DISTINCT contact_id, receive_date  FROM civicrm_contribution) as a ON civicrm_membership.contact_id = a.contact_id WHERE civicrm_membership.end_date = "2011-12-31" ');

Is there a way I can extract one distinct field and one non-distinct field in the subquery or is there a better way that does not use a subquery at all.

If I had my way I would simply add the receive_date column to the civicrm_membership table negating the need for a left join altogether, since thats the only column missing.  This would involve a hack and is out of the question.

Thanks in advance
Rob

[Andy Tolle]

SELECT mem.membership_type_id
FROM civicrm_membership as mem
LEFT JOIN civicrm_contribution as cont
ON mem.contact_id = cont.contact_id
WHERE mem.end_date = "2011-12-31"
GROUP BY contact_id

?

or:
SELECT mem.membership_type_id
FROM civicrm_membership as mem
WHERE mem.contact_id IN (SELECT DISTINCT (contact_id) FROM
civicrm_contribution)
AND mem.end_date = "2011-12-31"


-

Would be a lot easier if you told what you would like to achieve
exactly... it's hard for me to find it in the above text. Therefore
hard to find you something that actually works... anyway, if my guess
is right, the above will be a guide in the right direction.

[Rob Stocker]

Thanks Andy for taking the time,

civicrm_membership  (a)

+----------+-----------------------+
| contact_id | membership_type_id  |
+----------+-----------------------+
| 1       | 10                     |

| 2       | 20                     |
| 3       | 30                     |
____________________________________

civicrm_contribution  (b)

+----------+----------------+
| contact_id | receive_date |

+----------+----------------+
| 1       | 10-10-10        |
| 1       | 20-10-10        |
| 2       | 30-10-10        |
| 2       | 20-12-10        |
| 3       | 21-10-10        |
____________________________

my result should be : 
 10  10-10-10
 20  30-10-10
 30  21-10-10

not
10 10-10-10
10 20-10-10
20 30-10-10
20 20-12-10
30 21-10-10

Objective to link membership_type_id with receive_date using the a.contact_id and b.contect_id as links. But b.contact_id is not unique and I can't have duplicates . I hope this helps.

thanks
Rob

[Mark Dexter]

How about using the MAX function as follows:

SELECT a.membership_type_id, MAX(b.receive_date)
FROM civicrm_membership a
LEFT OUTER JOIN civicrm_contribution b
ON a.contact_id = b.contact_id
GROUP BY a.membership_type_id

Mark

> --
> You received this message because you are subscribed to the Google Groups
> "Joomla! General Development" group.
> To post to this group, send an email to joomla-de...@googlegroups.com.
> To unsubscribe from this group, send email to
> joomla-dev-gene...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/joomla-dev-general?hl=en-GB.
>

[Rob Stocker]

Hi Guys,

While not ideal, it will do for the moment. Basically I've got it working but don't understand why.

The code that gave me the result I desired was as follows:


SELECT civicrm_membership.membership_type_id , receive_date FROM civicrm_membership LEFT JOIN ( SELECT DISTINCT contact_id, receive_date FROM civicrm_contribution ) AS a ON civicrm_membership.contact_id = a.contact_id

WHERE civicrm_membership.end_date = "2011-12-31" GROUP BY a.contact_id, membership_type_id');

What did it was the second GROUP BY .

thanks for the help

Rob

[Andy Nagai]

You did not mention if you need the min or max receive date per membership record. Can't really tell looking at your example. You just want it to return some arbitrary date? If this is so then I question the need for the receive date field.

Andy Nagai