Hi, On Mon, 2005-10-10 at 10:05 -0400, James Walker wrote:

On 10-Oct-05, at 9:42 AM, Gordon Heydon wrote:

...

Hi,

I have a couple of issues that I have been playing with for a project and can't quite get my head around it from a SQL POV.

Is it possible to list all the rows in a table where one column has more than one occurrence of the same value.

In this can I am storing the ip address of some submitted data, and I need to check to see if they have submitted more than 1 row. I don't care about the ones with a single occurance, but I need to know about the ones that appear more that one.

I think you're looking for something like:

SELECT foo, bar, count(baz) as num FROM table GROUP BY foo, bar HAVING num > 1;

Thanks, this will return the consolidated list of results, but is there a way to still display the individual details. I think I may need to create a new page that will help identify dups. Thanks again. Gordon

Hi, thanks, I need to look at this some more but I think it may do the trick. Gordon. On Tue, 2005-10-11 at 09:17 +0100, Richard Grieve wrote:

Gordon

I just used this to clean some dirty data in a profile_values table which I had prepopulated. Is this what you're looking for?

select uid, fid, count(*) as num from profile_values where fid=72 group by uid having num >1

This returns

uid fid num 63 72 2 240 72 2 242 72 2 248 72 2 298 72 2 372 72 2 496 72 2 607 72 2 614 72 2 664 72 2 770 72 2 860 72 2 1057 72 2 1592 72 2

What would be cool would be

select * from profile_values where where fid=7 AND uid in (select uid from profile_values where fid=7 group by uid having count(*)>1)

But since mysql won't do nested selects I then used

select * from profile_values where where fid=7 AND uid in (63, 240, 242, 248 ,298, 372, 496, 607, 614, 664, 770, 860, 1057, 1592 )

Is this any good to you?

Rich

-----Original Message----- From: drupal-devel-bounces@drupal.org [mailto:drupal-devel-bounces@drupal.org] On Behalf Of Gordon Heydon Sent: 11 October 2005 00:11 To: drupal-devel@drupal.org Subject: Re: [drupal-devel] couple of SQL questions

Hi,

On Mon, 2005-10-10 at 10:05 -0400, James Walker wrote:

...

On 10-Oct-05, at 9:42 AM, Gordon Heydon wrote:

...

Hi,

I have a couple of issues that I have been playing with for a project and can't quite get my head around it from a SQL POV.

Is it possible to list all the rows in a table where one column has more than one occurrence of the same value.

In this can I am storing the ip address of some submitted data, and I need to check to see if they have submitted more than 1 row. I don't care about the ones with a single occurance, but I need to know about the ones that appear more that one.

I think you're looking for something like:

SELECT foo, bar, count(baz) as num FROM table GROUP BY foo, bar HAVING num > 1;

Thanks, this will return the consolidated list of results, but is there a way to still display the individual details.

I think I may need to create a new page that will help identify dups.

Thanks again. Gordon

!DSPAM:434b7bd57312068195507!

Hi, On Mon, 2005-10-10 at 10:19 -0400, Moshe Weitzman wrote:

...

My second question is that.

I have 2 tables, one is a teams table which has a list of entered teams. The other table is a list of members for each team. One of the columns is the name of the member. What I would like to do is create a select from the team table which has a single column with a comma separated list of members in a single field. This means I could sort and use it as a method of detecting entries of multiple teams with the same members.

Or if anyone has a better ideas on how to detect duplicate entries would be most appreciated.

That comma separated list can be done in MySQL 4.1+. See GROUP_CONCAT() function at http://dev.mysql.com/doc/mysql/en/group-by-functions.html. Eliminate dupes with a GROUP BY teamID.

Thanks for this. I will do some more investigation. If there any way of detecting which version of mysql from the module, then I can utilise this. Thanks. Gordon.