[drupal-devel] couple of SQL questions

[James Walker]

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;

(obviously you need to fill in the foo's and bar's to make it work  
right)

> 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.

I'm not sure I understand the question here as well... but it doesn't  
sound like something you can do cleanly at the SQL level...

--
James Walker :: http://walkah.net/