[drupal-devel] couple of SQL questions

[Gordon Heydon]

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 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
> 
> 
> !DSPAM:434b7bd57312068195507!
>