Subquery. Any help would be appreciated.

rriis · September 25th, 2005, 07:58 PM

Dataset below

Fields Num Name
            1 A
            1 A
            1 A
            1 B
            2 C
            3 D
            3 D

I think this would be a subquery, but I only want to return records where the number has more than one name associated with it. Like in the dataset above, I would not care about Num2 and Num3, but would want to return Num1. The trick is I want to filter out the duplicates... so in this case the final return I would be looking for is:

Fields Num Name
            1 A
            1 B

Any takers?

rick

SqlMenace · September 26th, 2005, 09:17 PM

select num,name
from table
group by num,name
having count(*) > 1

this should do it I think (it's getting late)
http://sqlservercode.blogspot.com/

gemsuis · September 29th, 2005, 08:58 AM

select num,name
from table_name
group by num,name
having num = 1;

try this

Jeff Mason · October 3rd, 2005, 12:04 PM

You are correct that you need a subquery, if I understand your requirement correctly. I don't think that either of the prior responses correctly give what you are looking for.

This is going to get a bit ugly, so let's take it in steps.

First off, this would be a lot easier if we had a table where the duplicates were removed. So, lets just group your data to remove them:

Code:

    SELECT Num, Name
    FROM yourtable
    GROUP BY Num, Name

will give the following output:

Code:

Now, let's pretend we have a table with this data in it. I'm going to call this table 'grouptable'.

We want to group this table by Num, and find those groups with more than one row in them:

Code:

    SELECT Num
    FROM grouptable
    GROUP BY Num
    HAVING count(*)>1

will return:

Code:

Now we need to select the rows in 'grouptable' where the Num value is in this last resultset, so we combine these two queries:

Code:

    SELECT Num, Name
    FROM grouptable
    WHERE Num IN 
        (SELECT Num
         FROM grouptable
         GROUP BY Num
         HAVING count(*)>1)

Of course, 'grouptable' doesn't actually exist in the database. But, we can materialize such a table at will. In fact that's what a SELECT statement does. This is called a 'derived table'. We can use a derived table anywhere we use a "real" one; the only requirement is that we must give it a name/alias.

So, now we just just substitute the definition of 'grouptable' in both places:

Code:

    SELECT Num, Name
    FROM 
        (SELECT Num, Name
         FROM yourtable
         GROUP BY Num, Name) as gt1
    WHERE Num IN 
        (SELECT Num
         FROM
            (SELECT Num, Name
             FROM yourtable
             GROUP BY Num, Name) as gt2
         GROUP BY Num
         HAVING count(*)>1)

yuck.

BTW, what is the primary key in the original table? You do have one, don't you? You're not going to get very far trying to maintain that table with those rows without one...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com