[drupal-devel] couple of SQL questions

[Richard Grieve]

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 at drupal.org
[mailto:drupal-devel-bounces at drupal.org] On Behalf Of Gordon Heydon
Sent: 11 October 2005 00:11
To: drupal-devel at 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